<!DOCTYPE html>
<html lang="zh-CN">
<head>
  <meta charset="UTF-8">
<meta name="viewport" content="width=device-width, initial-scale=1, maximum-scale=2">
<meta name="theme-color" content="#222">
<meta name="generator" content="Hexo 5.4.0">


  <link rel="apple-touch-icon" sizes="180x180" href="/hexo_blog/images/apple-touch-icon-next.png">
  <link rel="icon" type="image/png" sizes="32x32" href="/hexo_blog/images/favicon-32x32-next.png">
  <link rel="icon" type="image/png" sizes="16x16" href="/hexo_blog/images/favicon-16x16-next.png">
  <link rel="mask-icon" href="/hexo_blog/images/logo.svg" color="#222">

<link rel="stylesheet" href="/hexo_blog/css/main.css">



<link rel="stylesheet" href="https://cdn.jsdelivr.net/npm/@fortawesome/fontawesome-free@5.15.2/css/all.min.css">
  <link rel="stylesheet" href="https://cdn.jsdelivr.net/npm/nprogress@0.2.0/nprogress.css">
  <script src="https://cdn.jsdelivr.net/npm/nprogress@0.2.0/nprogress.js"></script>

<script class="hexo-configurations">
    var NexT = window.NexT || {};
    var CONFIG = {"hostname":"bone-nails.gitee.io","root":"/hexo_blog/","images":"/hexo_blog/images","scheme":"Gemini","version":"8.2.2","exturl":false,"sidebar":{"position":"left","display":"post","padding":18,"offset":12},"copycode":false,"bookmark":{"enable":false,"color":"#222","save":"auto"},"fancybox":false,"mediumzoom":false,"lazyload":false,"pangu":false,"comments":{"style":"tabs","active":null,"storage":true,"lazyload":false,"nav":null},"motion":{"enable":false,"async":false,"transition":{"post_block":"fadeIn","post_header":"fadeInDown","post_body":"fadeInDown","coll_header":"fadeInLeft","sidebar":"fadeInUp"}},"prism":false,"i18n":{"placeholder":"搜索...","empty":"没有找到任何搜索结果：${query}","hits_time":"找到 ${hits} 个搜索结果（用时 ${time} 毫秒）","hits":"找到 ${hits} 个搜索结果"},"path":"/hexo_blog/search.xml","localsearch":{"enable":true,"trigger":"auto","top_n_per_article":1,"unescape":true,"preload":true}};
  </script>
<meta name="description" content="数据库演练—-oracle11g 启动云服务器oracle11g容器">
<meta property="og:type" content="article">
<meta property="og:title" content="数据库演练oracle11g">
<meta property="og:url" content="https://bone-nails.gitee.io/2021/04/01/%E6%95%B0%E6%8D%AE%E5%BA%93%E6%BC%94%E7%BB%83oracle11g/index.html">
<meta property="og:site_name" content="Carson Zhao">
<meta property="og:description" content="数据库演练—-oracle11g 启动云服务器oracle11g容器">
<meta property="og:locale" content="zh_CN">
<meta property="og:image" content="https://imgconvert.csdnimg.cn/aHR0cHM6Ly96emotMTMwMDQwODQ5My5jb3MuYXAtY2hlbmdkdS5teXFjbG91ZC5jb20vJUU1JTlCJUJFJUU1JUJBJThBLzQ3LlBORw?x-oss-process=image/format,png">
<meta property="og:image" content="https://imgconvert.csdnimg.cn/aHR0cHM6Ly96emotMTMwMDQwODQ5My5jb3MuYXAtY2hlbmdkdS5teXFjbG91ZC5jb20vJUU1JTlCJUJFJUU1JUJBJThBLzQ4LlBORw?x-oss-process=image/format,png">
<meta property="og:image" content="https://imgconvert.csdnimg.cn/aHR0cHM6Ly96emotMTMwMDQwODQ5My5jb3MuYXAtY2hlbmdkdS5teXFjbG91ZC5jb20vJUU1JTlCJUJFJUU1JUJBJThBLzQ5LnBuZw?x-oss-process=image/format,png">
<meta property="article:published_time" content="2021-04-01T04:24:20.000Z">
<meta property="article:modified_time" content="2021-04-01T04:23:08.824Z">
<meta property="article:author" content="Carson Zhao">
<meta property="article:tag" content="Oracle">
<meta name="twitter:card" content="summary">
<meta name="twitter:image" content="https://imgconvert.csdnimg.cn/aHR0cHM6Ly96emotMTMwMDQwODQ5My5jb3MuYXAtY2hlbmdkdS5teXFjbG91ZC5jb20vJUU1JTlCJUJFJUU1JUJBJThBLzQ3LlBORw?x-oss-process=image/format,png">


<link rel="canonical" href="https://bone-nails.gitee.io/2021/04/01/%E6%95%B0%E6%8D%AE%E5%BA%93%E6%BC%94%E7%BB%83oracle11g/">


<script class="page-configurations">
  // https://hexo.io/docs/variables.html
  CONFIG.page = {
    sidebar: "",
    isHome : false,
    isPost : true,
    lang   : 'zh-CN'
  };
</script>
<title>数据库演练oracle11g | Carson Zhao</title>
  




  <noscript>
  <style>
  body { margin-top: 2rem; }

  .use-motion .menu-item,
  .use-motion .sidebar,
  .use-motion .post-block,
  .use-motion .pagination,
  .use-motion .comments,
  .use-motion .post-header,
  .use-motion .post-body,
  .use-motion .collection-header {
    visibility: visible;
  }

  .use-motion .header,
  .use-motion .site-brand-container .toggle,
  .use-motion .footer { opacity: initial; }

  .use-motion .site-title,
  .use-motion .site-subtitle,
  .use-motion .custom-logo-image {
    opacity: initial;
    top: initial;
  }

  .use-motion .logo-line {
    transform: scaleX(1);
  }

  .search-pop-overlay, .sidebar-nav { display: none; }
  .sidebar-panel { display: block; }
  </style>
</noscript>

<link rel="alternate" href="/hexo_blog/atom.xml" title="Carson Zhao" type="application/atom+xml">
</head>

<body itemscope itemtype="http://schema.org/WebPage">
  <div class="headband"></div>

  <main class="main">
    <header class="header" itemscope itemtype="http://schema.org/WPHeader">
      <div class="header-inner"><div class="site-brand-container">
  <div class="site-nav-toggle">
    <div class="toggle" aria-label="切换导航栏" role="button">
        <span class="toggle-line"></span>
        <span class="toggle-line"></span>
        <span class="toggle-line"></span>
    </div>
  </div>

  <div class="site-meta">

    <a href="/hexo_blog/" class="brand" rel="start">
      <i class="logo-line"></i>
      <h1 class="site-title">Carson Zhao</h1>
      <i class="logo-line"></i>
    </a>
  </div>

  <div class="site-nav-right">
    <div class="toggle popup-trigger">
        <i class="fa fa-search fa-fw fa-lg"></i>
    </div>
  </div>
</div>



<nav class="site-nav">
  <ul class="main-menu menu">
        <li class="menu-item menu-item-home"><a href="/hexo_blog/" rel="section"><i class="fa fa-home fa-fw"></i>首页</a></li>
        <li class="menu-item menu-item-about"><a href="/hexo_blog/about/" rel="section"><i class="fa fa-user fa-fw"></i>关于</a></li>
        <li class="menu-item menu-item-tags"><a href="/hexo_blog/tags/" rel="section"><i class="fa fa-tags fa-fw"></i>标签</a></li>
        <li class="menu-item menu-item-categories"><a href="/hexo_blog/categories/" rel="section"><i class="fa fa-th fa-fw"></i>分类</a></li>
      <li class="menu-item menu-item-search">
        <a role="button" class="popup-trigger"><i class="fa fa-search fa-fw"></i>搜索
        </a>
      </li>
  </ul>
</nav>



  <div class="search-pop-overlay">
    <div class="popup search-popup"><div class="search-header">
  <span class="search-icon">
    <i class="fa fa-search"></i>
  </span>
  <div class="search-input-container">
    <input autocomplete="off" autocapitalize="off" maxlength="80"
           placeholder="搜索..." spellcheck="false"
           type="search" class="search-input">
  </div>
  <span class="popup-btn-close" role="button">
    <i class="fa fa-times-circle"></i>
  </span>
</div>
<div class="search-result-container no-result">
  <div class="search-result-icon">
    <i class="fa fa-spinner fa-pulse fa-5x"></i>
  </div>
</div>

    </div>
  </div>

</div>
        
  
  <div class="toggle sidebar-toggle" role="button">
    <span class="toggle-line"></span>
    <span class="toggle-line"></span>
    <span class="toggle-line"></span>
  </div>

  <aside class="sidebar">

    <div class="sidebar-inner sidebar-nav-active sidebar-toc-active">
      <ul class="sidebar-nav">
        <li class="sidebar-nav-toc">
          文章目录
        </li>
        <li class="sidebar-nav-overview">
          站点概览
        </li>
      </ul>

      <div class="sidebar-panel-container">
        <!--noindex-->
        <div class="post-toc-wrap sidebar-panel">
            <div class="post-toc animated"><ol class="nav"><li class="nav-item nav-level-3"><a class="nav-link" href="#%E6%95%B0%E6%8D%AE%E5%BA%93%E6%BC%94%E7%BB%83%E2%80%94-oracle11g"><span class="nav-number">1.</span> <span class="nav-text">数据库演练—-oracle11g</span></a></li></ol></li><li class="nav-item nav-level-2"><a class="nav-link" href="#%E5%90%AF%E5%8A%A8%E4%BA%91%E6%9C%8D%E5%8A%A1%E5%99%A8oracle11g%E5%AE%B9%E5%99%A8"><span class="nav-number"></span> <span class="nav-text">启动云服务器oracle11g容器</span></a></li><li class="nav-item nav-level-2"><a class="nav-link" href="#%E9%87%8D%E5%90%AF%E6%95%B0%E6%8D%AE%E5%BA%93"><span class="nav-number"></span> <span class="nav-text">重启数据库</span></a></li><li class="nav-item nav-level-2"><a class="nav-link" href="#%E7%94%A8SQL-Developer%E8%BF%9E%E4%B8%8A%E4%BA%91%E6%9C%8D%E5%8A%A1%E5%99%A8%E6%95%B0%E6%8D%AE%E5%BA%93"><span class="nav-number"></span> <span class="nav-text">用SQL Developer连上云服务器数据库</span></a></li><li class="nav-item nav-level-2"><a class="nav-link" href="#%E7%94%A8%E6%88%B7%E5%9F%BA%E6%9C%AC%E6%93%8D%E4%BD%9C"><span class="nav-number"></span> <span class="nav-text">用户基本操作</span></a></li><li class="nav-item nav-level-2"><a class="nav-link" href="#%E5%BB%BA%E8%A1%A8"><span class="nav-number"></span> <span class="nav-text">建表</span></a></li><li class="nav-item nav-level-2"><a class="nav-link" href="#%E5%BB%BA%E8%A1%A8%E7%BA%A6%E6%9D%9F%EF%BC%88%E5%BB%BA%E8%A1%A8%E6%97%B6%EF%BC%89"><span class="nav-number"></span> <span class="nav-text">建表约束（建表时）</span></a></li><li class="nav-item nav-level-2"><a class="nav-link" href="#%E5%BB%BA%E8%A1%A8%E7%BA%A6%E6%9D%9F%EF%BC%88%E5%BB%BA%E8%A1%A8%E5%90%8E%EF%BC%89"><span class="nav-number"></span> <span class="nav-text">建表约束（建表后）</span></a></li><li class="nav-item nav-level-2"><a class="nav-link" href="#%E5%88%A9%E7%94%A8%E5%85%B6%E5%AE%83%E8%A1%A8%E5%88%9B%E5%BB%BA%E6%96%B0%E8%A1%A8"><span class="nav-number"></span> <span class="nav-text">利用其它表创建新表</span></a><ol class="nav-child"><li class="nav-item nav-level-3"><a class="nav-link" href="#%E5%A4%96%E9%94%AE%E7%BA%A6%E6%9D%9F%E8%A1%A5%E5%85%85%E8%AF%B4%E6%98%8E%EF%BC%9A%E7%BA%A6%E6%9D%9F%E4%B8%80%E5%BC%A0%E8%A1%A8%E7%9A%84%E5%AD%97%E6%AE%B5%E4%BE%9D%E8%B5%96%E4%BA%8E%E5%8F%A6%E4%B8%80%E5%BC%A0%E8%A1%A8%E7%9A%84%E5%AD%97%E6%AE%B5"><span class="nav-number">1.</span> <span class="nav-text">外键约束补充说明：约束一张表的字段依赖于另一张表的字段</span></a></li></ol></li><li class="nav-item nav-level-2"><a class="nav-link" href="#sql%E8%AF%AD%E8%A8%80%E5%88%86%E7%B1%BB"><span class="nav-number"></span> <span class="nav-text">sql语言分类</span></a></li><li class="nav-item nav-level-2"><a class="nav-link" href="#DDL%E8%AF%AD%E8%A8%80-%E5%8D%B3%E6%95%B0%E6%8D%AE%E5%AE%9A%E4%B9%89%E8%AF%AD%E8%A8%80%EF%BC%8C%E5%AE%9A%E4%B9%89%E6%95%B0%E6%8D%AE%E5%BA%93%E7%9A%84%E7%BB%93%E6%9E%84%EF%BC%88create-drop-alter%EF%BC%89"><span class="nav-number"></span> <span class="nav-text">DDL语言 即数据定义语言，定义数据库的结构（create drop alter）</span></a><ol class="nav-child"><li class="nav-item nav-level-3"><a class="nav-link" href="#create"><span class="nav-number">1.</span> <span class="nav-text">create</span></a></li><li class="nav-item nav-level-3"><a class="nav-link" href="#drop"><span class="nav-number">2.</span> <span class="nav-text">drop</span></a></li><li class="nav-item nav-level-3"><a class="nav-link" href="#alter"><span class="nav-number">3.</span> <span class="nav-text">alter</span></a></li></ol></li><li class="nav-item nav-level-2"><a class="nav-link" href="#to-date%E5%87%BD%E6%95%B0%EF%BC%9A%E5%B0%86%E5%AD%97%E7%AC%A6%E4%B8%B2%E7%B1%BB%E5%9E%8B%E7%9A%84%E5%AD%97%E7%AC%A6%E4%B8%B2%E8%BD%AC%E6%8D%A2%E6%88%90"><span class="nav-number"></span> <span class="nav-text">to_date函数：将字符串类型的字符串转换成</span></a></li><li class="nav-item nav-level-2"><a class="nav-link" href="#%E6%94%B9sqldeveloper%E9%BB%98%E8%AE%A4%E6%97%B6%E9%97%B4%E6%A0%BC%E5%BC%8F"><span class="nav-number"></span> <span class="nav-text">改sqldeveloper默认时间格式</span></a></li><li class="nav-item nav-level-2"><a class="nav-link" href="#DML%E8%AF%AD%E8%A8%80%EF%BC%88%E6%95%B0%E6%8D%AE%E6%93%8D%E7%BA%B5%E8%AF%AD%E8%A8%80%EF%BC%8Cinsert%EF%BC%8Cupdate%EF%BC%8Cdelete%EF%BC%89"><span class="nav-number"></span> <span class="nav-text">DML语言（数据操纵语言，insert，update，delete）</span></a><ol class="nav-child"><li class="nav-item nav-level-3"><a class="nav-link" href="#%E6%B3%A8%E6%84%8F%E8%BF%99%E4%B8%89%E4%B8%AA%E6%8C%87%E4%BB%A4%E9%83%BD%E4%B8%8D%E9%9C%80%E8%A6%81%E5%8A%A0table"><span class="nav-number">1.</span> <span class="nav-text">注意这三个指令都不需要加table</span></a></li><li class="nav-item nav-level-3"><a class="nav-link" href="#insert-%E5%AD%97%E6%AE%B5%E6%8F%92%E5%85%A5"><span class="nav-number">2.</span> <span class="nav-text">insert 字段插入</span></a></li><li class="nav-item nav-level-3"><a class="nav-link" href="#update-%E6%95%B0%E6%8D%AE%E6%9B%B4%E6%96%B0"><span class="nav-number">3.</span> <span class="nav-text">update 数据更新</span></a></li><li class="nav-item nav-level-3"><a class="nav-link" href="#delete"><span class="nav-number">4.</span> <span class="nav-text">delete</span></a></li></ol></li><li class="nav-item nav-level-2"><a class="nav-link" href="#%E6%95%B0%E6%8D%AE%E5%BA%93%E8%AE%BE%E8%AE%A1%E4%B8%89%E5%A4%A7%E8%8C%83%E5%BC%8F-%E5%87%8F%E5%B0%91%E5%86%97%E4%BD%99%EF%BC%8C%E6%8F%90%E9%AB%98%E5%AD%98%E5%82%A8%E6%95%88%E7%8E%87%EF%BC%8C%E4%BD%86%E7%89%BA%E7%89%B2%E6%9F%A5%E8%AF%A2%E6%80%A7%E8%83%BD"><span class="nav-number"></span> <span class="nav-text">数据库设计三大范式(减少冗余，提高存储效率，但牺牲查询性能)</span></a></li><li class="nav-item nav-level-2"><a class="nav-link" href="#%E6%95%B0%E6%8D%AE%E5%BA%93%E4%BA%8B%E5%8A%A1"><span class="nav-number"></span> <span class="nav-text">数据库事务</span></a></li><li class="nav-item nav-level-2"><a class="nav-link" href="#%E5%8D%95%E8%A1%A8%E6%9F%A5%E8%AF%A2%E8%AF%AD%E5%8F%A5"><span class="nav-number"></span> <span class="nav-text">单表查询语句</span></a><ol class="nav-child"><li class="nav-item nav-level-3"><a class="nav-link" href="#%E6%9F%A5%E8%AF%A2%E5%BD%93%E5%89%8D%E7%94%A8%E6%88%B7%E6%89%80%E6%9C%89%E7%9A%84%E8%A1%A8"><span class="nav-number">1.</span> <span class="nav-text">查询当前用户所有的表</span></a></li><li class="nav-item nav-level-3"><a class="nav-link" href="#%E6%9F%A5%E8%AF%A2%E6%98%AF%E5%90%A6%E6%9C%89%E6%9F%90%E5%BC%A0%E8%A1%A8%EF%BC%88%E6%B3%A8%E6%84%8F%E5%BF%85%E9%A1%BB%E5%A4%A7%E5%86%99%EF%BC%89"><span class="nav-number">2.</span> <span class="nav-text">查询是否有某张表（注意必须大写）</span></a></li><li class="nav-item nav-level-3"><a class="nav-link" href="#%E6%9F%A5%E8%AF%A2%E7%B3%BB%E7%BB%9F%E6%89%80%E6%9C%89%E7%9A%84%E7%94%A8%E6%88%B7"><span class="nav-number">3.</span> <span class="nav-text">查询系统所有的用户</span></a></li><li class="nav-item nav-level-3"><a class="nav-link" href="#%E6%9F%A5%E8%AF%A2emp%E8%A1%A8%E6%89%80%E6%9C%89%E5%AD%97%E6%AE%B5%EF%BC%9A"><span class="nav-number">4.</span> <span class="nav-text">查询emp表所有字段：</span></a></li><li class="nav-item nav-level-3"><a class="nav-link" href="#%E6%9F%A5%E8%AF%A2%E6%8C%87%E5%AE%9A%E5%AD%97%E6%AE%B5%EF%BC%9A"><span class="nav-number">5.</span> <span class="nav-text">查询指定字段：</span></a></li><li class="nav-item nav-level-3"><a class="nav-link" href="#%E5%88%AB%E5%90%8D%EF%BC%9A"><span class="nav-number">6.</span> <span class="nav-text">别名：</span></a></li><li class="nav-item nav-level-3"><a class="nav-link" href="#%E5%AD%97%E7%AC%A6%E6%8B%BC%E6%8E%A5-%EF%BC%9A"><span class="nav-number">7.</span> <span class="nav-text">字符拼接||：</span></a></li><li class="nav-item nav-level-3"><a class="nav-link" href="#%E6%9F%A5%E8%AF%A2%E7%BB%93%E6%9E%9C%E5%8E%BB%E9%87%8D%EF%BC%9A"><span class="nav-number">8.</span> <span class="nav-text">查询结果去重：</span></a></li><li class="nav-item nav-level-3"><a class="nav-link" href="#order-by%E6%95%B0%E6%8D%AE%E6%8E%92%E5%BA%8F%EF%BC%8C%E9%BB%98%E8%AE%A4%E6%AD%A3%E5%BA%8F%EF%BC%8C%E5%80%92%E5%BA%8F%E5%8A%A0%E4%B8%8Adesc"><span class="nav-number">9.</span> <span class="nav-text">order by数据排序，默认正序，倒序加上desc:</span></a></li><li class="nav-item nav-level-3"><a class="nav-link" href="#%E5%A4%9A%E5%AD%97%E6%AE%B5%E6%8E%92%E5%BA%8F%EF%BC%9A"><span class="nav-number">10.</span> <span class="nav-text">多字段排序：</span></a></li><li class="nav-item nav-level-3"><a class="nav-link" href="#%E5%AD%97%E6%AE%B5%E5%9B%9B%E5%88%99%E8%BF%90%E7%AE%97%EF%BC%9A"><span class="nav-number">11.</span> <span class="nav-text">字段四则运算：+ - * &#x2F; %</span></a></li><li class="nav-item nav-level-3"><a class="nav-link" href="#where-%E5%85%B3%E9%94%AE%E5%AD%97%EF%BC%8C%E6%8F%90%E4%BE%9B%E7%AD%9B%E9%80%89"><span class="nav-number">12.</span> <span class="nav-text">where 关键字，提供筛选</span></a></li><li class="nav-item nav-level-3"><a class="nav-link" href="#between-and-%E5%8F%96%E8%8C%83%E5%9B%B4%E5%86%85%E7%9A%84%E5%80%BC%EF%BC%8C%E9%97%AD%E5%8C%BA%E9%97%B4"><span class="nav-number">13.</span> <span class="nav-text">between  and  (取范围内的值，闭区间)</span></a></li><li class="nav-item nav-level-3"><a class="nav-link" href="#in%EF%BC%88%E6%9E%9A%E4%B8%BEin%E4%B8%AD%E7%9A%84%E5%80%BC%EF%BC%89"><span class="nav-number">14.</span> <span class="nav-text">in（枚举in中的值）</span></a></li><li class="nav-item nav-level-3"><a class="nav-link" href="#like%E6%A8%A1%E7%B3%8A%E6%9F%A5%E8%AF%A2"><span class="nav-number">15.</span> <span class="nav-text">like模糊查询</span></a></li></ol></li><li class="nav-item nav-level-2"><a class="nav-link" href="#%E5%AD%97%E7%AC%A6%E4%B8%B2%E5%87%BD%E6%95%B0"><span class="nav-number"></span> <span class="nav-text">字符串函数</span></a><ol class="nav-child"><li class="nav-item nav-level-3"><a class="nav-link" href="#%E8%8E%B7%E5%8F%96%E5%AD%97%E7%AC%A6%E4%B8%B2%E9%95%BF%E5%BA%A6"><span class="nav-number">1.</span> <span class="nav-text">获取字符串长度</span></a></li></ol></li><li class="nav-item nav-level-2"><a class="nav-link" href="#%E6%9B%BF%E6%8D%A2"><span class="nav-number"></span> <span class="nav-text">替换</span></a><ol class="nav-child"><li class="nav-item nav-level-3"><a class="nav-link" href="#%E9%A6%96%E5%AD%97%E6%AF%8D%E5%A4%A7%E5%86%99"><span class="nav-number">1.</span> <span class="nav-text">首字母大写</span></a></li><li class="nav-item nav-level-3"><a class="nav-link" href="#%E5%BC%BA%E5%88%B6%E5%B0%8F%E5%86%99"><span class="nav-number">2.</span> <span class="nav-text">强制小写</span></a></li><li class="nav-item nav-level-3"><a class="nav-link" href="#%E5%BC%BA%E5%88%B6%E5%A4%A7%E5%86%99"><span class="nav-number">3.</span> <span class="nav-text">强制大写</span></a></li><li class="nav-item nav-level-3"><a class="nav-link" href="#%E8%BF%87%E6%BB%A4%E9%A6%96%E5%B0%BE%E7%A9%BA%E6%A0%BC"><span class="nav-number">4.</span> <span class="nav-text">过滤首尾空格</span></a></li><li class="nav-item nav-level-3"><a class="nav-link" href="#%E5%B7%A6%E8%A3%81%E5%89%AA-%E7%AC%AC%E4%B8%80%E4%B8%AA%E5%8F%82%E6%95%B0%E4%B8%AD%E5%8E%BB%E6%8E%89zyz%E6%9D%A5%E6%98%BE%E7%A4%BA%EF%BC%8Czyz%E5%BF%85%E9%A1%BB%E4%BD%8D%E4%BA%8E%E6%9C%80%E5%B7%A6%E8%BE%B9"><span class="nav-number">5.</span> <span class="nav-text">左裁剪(第一个参数中去掉zyz来显示，zyz必须位于最左边)</span></a></li><li class="nav-item nav-level-3"><a class="nav-link" href="#%E5%8F%B3%E8%A3%81%E5%89%AA-%E7%AC%AC%E4%B8%80%E4%B8%AA%E5%8F%82%E6%95%B0%E4%B8%AD%E5%8E%BB%E6%8E%89handsome%E6%9D%A5%E6%98%BE%E7%A4%BA%EF%BC%8Chandsome%E5%BF%85%E9%A1%BB%E4%BD%8D%E4%BA%8E%E6%9C%80%E5%8F%B3%E8%BE%B9"><span class="nav-number">6.</span> <span class="nav-text">右裁剪(第一个参数中去掉handsome来显示，handsome必须位于最右边)</span></a></li><li class="nav-item nav-level-3"><a class="nav-link" href="#%E6%8C%89%E5%AD%97%E7%AC%A6%E7%BF%BB%E8%AF%91-%E7%95%A5"><span class="nav-number">7.</span> <span class="nav-text">按字符翻译(略)</span></a></li><li class="nav-item nav-level-3"><a class="nav-link" href="#%E6%9F%A5%E6%89%BE%E5%AD%97%E7%AC%A6%E4%B8%B2%E4%BD%8D%E7%BD%AE%EF%BC%88%E7%95%A5%EF%BC%89"><span class="nav-number">8.</span> <span class="nav-text">查找字符串位置（略）</span></a></li><li class="nav-item nav-level-3"><a class="nav-link" href="#%E6%88%AA%E5%8F%96%E5%AD%97%E7%AC%A6%E4%B8%B2%EF%BC%88%E5%88%9D%E8%AF%86%E4%B8%8B%E6%A0%87%E4%B8%BA1%EF%BC%89"><span class="nav-number">9.</span> <span class="nav-text">截取字符串（初识下标为1）</span></a></li><li class="nav-item nav-level-3"><a class="nav-link" href="#%E5%AD%97%E7%AC%A6%E4%B8%B2%E8%BF%9E%E6%8E%A5-%E7%B1%BB%E4%BC%BC%E4%BA%8E-%E5%8C%BA%E5%88%ABconcat%E5%8F%AA%E8%83%BD%E6%9C%89%E4%B8%A4%E4%B8%AA%E5%8F%82%E6%95%B0"><span class="nav-number">10.</span> <span class="nav-text">字符串连接(类似于||,区别concat只能有两个参数)</span></a></li></ol></li><li class="nav-item nav-level-2"><a class="nav-link" href="#%E6%95%B0%E5%AD%A6%E5%87%BD%E6%95%B0"><span class="nav-number"></span> <span class="nav-text">数学函数</span></a></li><li class="nav-item nav-level-2"><a class="nav-link" href="#%E6%97%A5%E6%9C%9F%E6%95%B0%E6%8D%AE"><span class="nav-number"></span> <span class="nav-text">日期数据</span></a></li><li class="nav-item nav-level-2"><a class="nav-link" href="#%E5%B8%B8%E7%94%A8%E8%81%9A%E5%90%88%E5%87%BD%E6%95%B0%EF%BC%88max-min-avg-sum-count%EF%BC%89"><span class="nav-number"></span> <span class="nav-text">常用聚合函数（max,min,avg,sum,count）</span></a></li><li class="nav-item nav-level-2"><a class="nav-link" href="#%E5%8D%95%E8%A1%A8%E6%9F%A5%E8%AF%A2%E6%BC%94%E7%BB%83%EF%BC%887%EF%BC%8C9%EF%BC%8C10-14-15%EF%BC%89"><span class="nav-number"></span> <span class="nav-text">单表查询演练（7，9，10,14,15）</span></a></li><li class="nav-item nav-level-2"><a class="nav-link"><span class="nav-number"></span> <span class="nav-text"></span></a></li><li class="nav-item nav-level-2"><a class="nav-link" href="#%E5%88%86%E7%BB%84%E6%9F%A5%E8%AF%A2-%E5%88%86%E7%BB%84%EF%BC%9A%E5%B0%86%E5%85%B7%E6%9C%89%E7%9B%B8%E5%90%8C%E6%8C%87%E5%AE%9A%E5%AD%97%E6%AE%B5%E5%80%BC%E7%9A%84%E8%AE%B0%E5%BD%95%E5%BD%92%E7%BA%B3%E4%B8%BA%E5%90%8C%E4%B8%80%E7%BB%84"><span class="nav-number"></span> <span class="nav-text">分组查询(分组：将具有相同指定字段值的记录归纳为同一组)</span></a></li><li class="nav-item nav-level-2"><a class="nav-link" href="#sql%E8%AF%AD%E5%8F%A5%E6%89%A7%E8%A1%8C%E9%A1%BA%E5%BA%8F"><span class="nav-number"></span> <span class="nav-text">sql语句执行顺序</span></a></li><li class="nav-item nav-level-2"><a class="nav-link" href="#rownum-%E6%AF%8F%E4%B8%AA%E8%A1%A8%E9%83%BD%E6%9C%89%E4%B8%80%E4%B8%AA%E9%9A%90%E8%97%8F%E7%9A%84%E5%AD%97%E6%AE%B5rownum%EF%BC%8C%E4%B8%8B%E6%A0%87%E4%BB%8E1%E5%BC%80%E5%A7%8B%E9%80%92%E5%A2%9E"><span class="nav-number"></span> <span class="nav-text">rownum(每个表都有一个隐藏的字段rownum，下标从1开始递增)</span></a></li><li class="nav-item nav-level-2"><a class="nav-link" href="#%E4%BB%80%E4%B9%88%E6%98%AF%E7%AC%9B%E5%8D%A1%E5%B0%94%E7%A7%AF"><span class="nav-number"></span> <span class="nav-text">什么是笛卡尔积</span></a></li><li class="nav-item nav-level-2"><a class="nav-link" href="#%E5%A4%9A%E8%A1%A8%E6%9F%A5%E8%AF%A2-sql99%EF%BC%88%E5%A2%9E%E5%8A%A0%E4%BA%86SQL92%E6%B2%A1%E6%9C%89%E7%9A%84%E5%85%B3%E9%94%AE%E5%AD%97-%E5%8F%AF%E8%AF%BB%E6%80%A7%E5%A2%9E%E5%BC%BA%EF%BC%89"><span class="nav-number"></span> <span class="nav-text">多表查询 sql99（增加了SQL92没有的关键字 可读性增强）</span></a></div>
        </div>
        <!--/noindex-->

        <div class="site-overview-wrap sidebar-panel">
          <div class="site-author site-overview-item animated" itemprop="author" itemscope itemtype="http://schema.org/Person">
    <img class="site-author-image" itemprop="image" alt="Carson Zhao"
      src="/hexo_blog/images/1.png">
  <p class="site-author-name" itemprop="name">Carson Zhao</p>
  <div class="site-description" itemprop="description"></div>
</div>
<div class="site-state-wrap site-overview-item animated">
  <nav class="site-state">
      <div class="site-state-item site-state-posts">
          <a href="/hexo_blog/archives">
          <span class="site-state-item-count">59</span>
          <span class="site-state-item-name">日志</span>
        </a>
      </div>
      <div class="site-state-item site-state-categories">
            <a href="/hexo_blog/categories/">
          
        <span class="site-state-item-count">14</span>
        <span class="site-state-item-name">分类</span></a>
      </div>
      <div class="site-state-item site-state-tags">
            <a href="/hexo_blog/tags/">
          
        <span class="site-state-item-count">35</span>
        <span class="site-state-item-name">标签</span></a>
      </div>
  </nav>
</div>
  <div class="cc-license site-overview-item animated" itemprop="license">
    <a href="https://creativecommons.org/licenses/by-nc-sa/4.0/deed.zh" class="cc-opacity" rel="noopener" target="_blank"><img src="/hexo_blog/images/cc-by-nc-sa.svg" alt="Creative Commons"></a>
  </div>



        </div>
      </div>
    </div>
  </aside>
  <div class="sidebar-dimmer"></div>


    </header>

    
  <div class="back-to-top" role="button">
    <i class="fa fa-arrow-up"></i>
    <span>0%</span>
  </div>

<noscript>
  <div class="noscript-warning">Theme NexT works best with JavaScript enabled</div>
</noscript>


    <div class="main-inner post posts-expand">


  


<div class="post-block">
  
  

  <article itemscope itemtype="http://schema.org/Article" class="post-content" lang="zh-CN">
    <link itemprop="mainEntityOfPage" href="https://bone-nails.gitee.io/2021/04/01/%E6%95%B0%E6%8D%AE%E5%BA%93%E6%BC%94%E7%BB%83oracle11g/">

    <span hidden itemprop="author" itemscope itemtype="http://schema.org/Person">
      <meta itemprop="image" content="/hexo_blog/images/1.png">
      <meta itemprop="name" content="Carson Zhao">
      <meta itemprop="description" content="">
    </span>

    <span hidden itemprop="publisher" itemscope itemtype="http://schema.org/Organization">
      <meta itemprop="name" content="Carson Zhao">
    </span>
      <header class="post-header">
        <h1 class="post-title" itemprop="name headline">
          数据库演练oracle11g
        </h1>

        <div class="post-meta-container">
          <div class="post-meta">
    <span class="post-meta-item">
      <span class="post-meta-item-icon">
        <i class="far fa-calendar"></i>
      </span>
      <span class="post-meta-item-text">发表于</span>
      

      <time title="创建时间：2021-04-01 12:24:20 / 修改时间：12:23:08" itemprop="dateCreated datePublished" datetime="2021-04-01T12:24:20+08:00">2021-04-01</time>
    </span>
    <span class="post-meta-item">
      <span class="post-meta-item-icon">
        <i class="far fa-folder"></i>
      </span>
      <span class="post-meta-item-text">分类于</span>
        <span itemprop="about" itemscope itemtype="http://schema.org/Thing">
          <a href="/hexo_blog/categories/Oracle/" itemprop="url" rel="index"><span itemprop="name">Oracle</span></a>
        </span>
    </span>

  
    <span class="post-meta-item" title="阅读次数" id="busuanzi_container_page_pv" style="display: none;">
      <span class="post-meta-item-icon">
        <i class="far fa-eye"></i>
      </span>
      <span class="post-meta-item-text">阅读次数：</span>
      <span id="busuanzi_value_page_pv"></span>
    </span>
    <span class="post-meta-item" title="本文字数">
      <span class="post-meta-item-icon">
        <i class="far fa-file-word"></i>
      </span>
      <span class="post-meta-item-text">本文字数：</span>
      <span>36k</span>
    </span>
    <span class="post-meta-item" title="阅读时长">
      <span class="post-meta-item-icon">
        <i class="far fa-clock"></i>
      </span>
      <span class="post-meta-item-text">阅读时长 &asymp;</span>
      <span>33 分钟</span>
    </span>
</div>

        </div>
      </header>

    
    
    
    <div class="post-body" itemprop="articleBody">
        <h3 id="数据库演练—-oracle11g"><a href="#数据库演练—-oracle11g" class="headerlink" title="数据库演练—-oracle11g"></a>数据库演练—-oracle11g</h3><span id="more"></span>
<h2 id="启动云服务器oracle11g容器"><a href="#启动云服务器oracle11g容器" class="headerlink" title="启动云服务器oracle11g容器"></a>启动云服务器oracle11g容器</h2><p><code>docker exec -it oracle11g /bin/bash</code></p>
<h2 id="重启数据库"><a href="#重启数据库" class="headerlink" title="重启数据库"></a>重启数据库</h2><p><code>docker exec -it oracle11g /bin/bash</code>启动容器<br><code>source /etc/profile</code>  配置文件生效<br>输入<code>sqlplus / as sysdba</code> 回车。<br>输入<code>shutdown immediate</code> 回车关闭数据库<br>等待提示数据库关闭完成后，<br>输入<code>startup</code>回车。<br>等待数据库开启完成。</p>
<h2 id="用SQL-Developer连上云服务器数据库"><a href="#用SQL-Developer连上云服务器数据库" class="headerlink" title="用SQL Developer连上云服务器数据库"></a>用SQL Developer连上云服务器数据库</h2><ol>
<li><p>oracle关系型数据库</p>
<ol>
<li>关系型数据库：sqlserver  mysql  oracle  access<ol>
<li>mysql单表支持500w条数据，轻量级数据库</li>
<li>oracle单表支持1亿条数据（8i 9i 10g 11g 12c(loud)）,贵但提供技术支持</li>
<li>oracle权限机制：所有用户需要做什么操作都需要被赋予相应的权限<ol>
<li>管理员用户：可以创建用户，赋予普通用户权限</li>
<li>普通用户：所有权限都来自管理员</li>
</ol>
</li>
</ol>
</li>
<li>大型数据库：nosql redis(键值对数据库)</li>
<li>数据库中的每一张表由字段和字段的值组成，多个字段的值组成一行数据，多行记录组成表中所有数据。</li>
<li>数据库中存储的是数据和数据之间的联系。<h2 id="用户基本操作"><a href="#用户基本操作" class="headerlink" title="用户基本操作"></a>用户基本操作</h2></li>
</ol>
</li>
<li><p>创建用户zzj并设置密码为123456，状态为unlock：<code>create user zzj identified by 123456 account unlock;</code>;</p>
<ol>
<li><code>identified by</code>设置密码</li>
<li><code>account unlock</code> 不锁定用户,<code>lock</code>锁定用户，无法登陆和操作。默认<code>unlock</code></li>
<li>赋予zzj登录权限：<code>grant connect to zzj;</code>,否则无法连接试用zzj用户。  </li>
<li>赋予zzj资源权限：<code>grant resource to zzj;</code>，否则无法创建表、视图等等。<br>至此可以新建并连接到zzj用户：<br><img src="https://imgconvert.csdnimg.cn/aHR0cHM6Ly96emotMTMwMDQwODQ5My5jb3MuYXAtY2hlbmdkdS5teXFjbG91ZC5jb20vJUU1JTlCJUJFJUU1JUJBJThBLzQ3LlBORw?x-oss-process=image/format,png">  </li>
<li>权限收回：<code>revoke connect from zzj;</code>和<code>revoke resource from zzj;</code></li>
<li><code>grant dba to zzj</code>赋予普通用户管理员权限，不要使用，权限无法收回。</li>
</ol>
</li>
<li><p>scott用户（默认锁定）</p>
<ol>
<li><code>alter user scott account unlock;</code>要使用scott用户登录需要修改scott用户状态为解锁状态。</li>
<li><code>alter user scott identified by 123456</code>修改scott用户密码</li>
<li>创建连接并登录，连接名为scott</li>
<li>执行<code>select * from emp</code>得到一张员工表，其中字段：sal(薪水)，comm(奖金)，deptno（部门）。<h2 id="建表"><a href="#建表" class="headerlink" title="建表"></a>建表</h2></li>
</ol>
</li>
<li><p>字段的数据类型：限制插入该字段的值的类型</p>
<ol>
<li>number型</li>
<li>字符类型<ol>
<li>char</li>
<li>varchar2</li>
</ol>
</li>
<li>日期类型</li>
<li>例：  <figure class="highlight plain"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br><span class="line">6</span><br><span class="line">7</span><br><span class="line">8</span><br><span class="line">9</span><br><span class="line">10</span><br><span class="line">11</span><br><span class="line">12</span><br><span class="line">13</span><br></pre></td><td class="code"><pre><span class="line">create table stu(</span><br><span class="line">sno number(5),  --括号中的数字表示最多支持5位</span><br><span class="line">height number(4,1),  --总共有四位，小数部分占1位</span><br><span class="line">sname char(15),  -- 括号中限制的长度单位是字节，英文1字节，中文2~4字节（utf-8占3字节）</span><br><span class="line">                -- unicode万国码，将所有字符集编码统一起来。（缺陷：中英文占用字节一样，如果是英文就会造成空间浪费）</span><br><span class="line">                -- utf-8:所有ASCLL码中的字符占一个字节，中文占三个字节</span><br><span class="line">loc varchar2(200),</span><br><span class="line">-- varchar2和char区别</span><br><span class="line">-- char是定长类型（大小固定，未填充字符的部分以空格代替，可能造成浪费），varchar2是不定长类型（数据占用空间大小由数据长度决定）</span><br><span class="line">-- char牺牲了空间，提高了数据的传递速度，适用于字段值长度不一。</span><br><span class="line">-- varchar2节省了空间，但数据传递速度比char慢，适用于字段值长度差不多（读取的时候字段长度差异比较小，数据传递速度不会太慢，同时节省了空间）。</span><br><span class="line">bir_date date</span><br><span class="line">)</span><br></pre></td></tr></table></figure></li>
</ol>
</li>
<li><p>注意字段之间逗号隔开，最后一个字段不需要加逗号，创表过程中不能使用中文。</p>
</li>
<li><p>利用图形界面插入数据：<br><img src="https://imgconvert.csdnimg.cn/aHR0cHM6Ly96emotMTMwMDQwODQ5My5jb3MuYXAtY2hlbmdkdS5teXFjbG91ZC5jb20vJUU1JTlCJUJFJUU1JUJBJThBLzQ4LlBORw?x-oss-process=image/format,png"></p>
<h2 id="建表约束（建表时）"><a href="#建表约束（建表时）" class="headerlink" title="建表约束（建表时）"></a>建表约束（建表时）</h2><p>student结构：</p>
<figure class="highlight plain"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br><span class="line">6</span><br><span class="line">7</span><br></pre></td><td class="code"><pre><span class="line">create table student(</span><br><span class="line">stuid number(8) primary key,  -- 主键约束</span><br><span class="line">stuname varchar2(15) not null unique, -- 非空约束，唯一约束</span><br><span class="line">gender varchar2(3) check(gender&#x3D;&#39;男&#39; or gender &#x3D; &#39;女&#39;), -- 检查约束</span><br><span class="line">stu_address varchar2(200) default &#39;默认不详&#39;, --默认约束  </span><br><span class="line">birdate date</span><br><span class="line">);</span><br></pre></td></tr></table></figure>
<h2 id="建表约束（建表后）"><a href="#建表约束（建表后）" class="headerlink" title="建表约束（建表后）"></a>建表约束（建表后）</h2><ol>
<li>主键约束：<ol>
<li>字段值不可为空，不可重复，一个表只能有一个主键约束。</li>
<li>给stuid’增加主键约束：<code>alter table student add constraint py_key primary key(stuid);</code></li>
</ol>
</li>
<li>检查约束：是否符合条件<ol>
<li>给gender添加检查约束：<code>alter table student add constraint stu_ck check (gender=&#39;男&#39; or gender=&#39;女&#39;);</code></li>
</ol>
</li>
<li>唯一约束: 字段值不能重复<ol>
<li>给stuname添加唯一约束：<code>alter table student add constraint un_stu unique(stuname);</code></li>
</ol>
</li>
<li>默认约束：<ol>
<li><code>alter table student modify (stu_address default &#39;目的不详&#39;);</code></li>
</ol>
</li>
<li>非空约束：<ol>
<li><code>alter table student modify (stuname not null);</code></li>
</ol>
</li>
<li>oracle中约束只能删除和添加，不能修改<ol>
<li>先删再加：<code>alter table student drop constraint 约束名;</code></li>
</ol>
</li>
<li>外键约束以及建表约束</li>
</ol>
<p> 创建一个scores表（依赖于student表）</p>
<figure class="highlight plain"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br><span class="line">6</span><br><span class="line">7</span><br><span class="line">8</span><br><span class="line">9</span><br><span class="line">10</span><br></pre></td><td class="code"><pre><span class="line">create table scores(</span><br><span class="line">scid number primary key,</span><br><span class="line">term varchar2(2) check(term&#x3D;&#39;S1&#39; or term&#x3D;&#39;S2&#39;),</span><br><span class="line">stuid number(10)not null,</span><br><span class="line">examno varchar2(7)not null,考试科目编号</span><br><span class="line">writtenscore number(4,1)default &#39;0&#39; not null,笔试成绩</span><br><span class="line">labscore number(4,1)default &#39;0&#39; not null,</span><br><span class="line">-- 添加外键约束</span><br><span class="line">constraint fk_scores_stu foreign key(stuid) references   student(stuid)</span><br><span class="line">)</span><br></pre></td></tr></table></figure>
<h2 id="利用其它表创建新表"><a href="#利用其它表创建新表" class="headerlink" title="利用其它表创建新表"></a>利用其它表创建新表</h2><ol>
<li>create table emp1 as select * from scott.emp; 只可以复制表的数据，字段和名称，不可以复制约束和外键。</li>
<li>只想复制表的字段和表名，不想复制表的数据<br>create table emp2 as select * from scott.emp where 1=2;1=2返回false，表中没有一条数据满足，自然查询不到数据。</li>
</ol>
<h3 id="外键约束补充说明：约束一张表的字段依赖于另一张表的字段"><a href="#外键约束补充说明：约束一张表的字段依赖于另一张表的字段" class="headerlink" title="外键约束补充说明：约束一张表的字段依赖于另一张表的字段"></a>外键约束补充说明：约束一张表的字段依赖于另一张表的字段</h3><ol>
<li>studengt父表，scores子表<ol>
<li>子表在插入数据时，必须在父表中找到关联的信息（即父表中必须有此stuid），否则子表无法插入。</li>
<li>错误案例：insert into scores values(1,’S1’,10,’高数’,99.0,89.0)</li>
<li>如果父表在子表中有关联记录，则父表记录无法直接删除。</li>
<li>一般来说当前表（scores）在建立外键约束的时候，关联的字段在另一张表（student）中一定是主键。<h2 id="sql语言分类"><a href="#sql语言分类" class="headerlink" title="sql语言分类"></a>sql语言分类</h2></li>
</ol>
</li>
</ol>
</li>
<li><p>DDL：数据定义语言</p>
<ol>
<li>用来定义数据库对象：数据库，表，列等。关键字：create, drop,alter 等</li>
</ol>
</li>
<li><p>DML：数据操作语言</p>
<ol>
<li>用来对数据库中表的数据进行增删改。关键字：insert, delete, update 等</li>
</ol>
</li>
<li><p>DQL：数据查询语言</p>
<ol>
<li>用来查询数据库中表的记录(数据)。关键字：select, where 等</li>
</ol>
</li>
<li><p>DCL：数据控制语言</p>
<ol>
<li>用来定义数据库的访问权限和安全级别，及创建用户。关键字：grant,revoke 等<h2 id="DDL语言-即数据定义语言，定义数据库的结构（create-drop-alter）"><a href="#DDL语言-即数据定义语言，定义数据库的结构（create-drop-alter）" class="headerlink" title="DDL语言 即数据定义语言，定义数据库的结构（create drop alter）"></a>DDL语言 即数据定义语言，定义数据库的结构（create drop alter）</h2><h3 id="create"><a href="#create" class="headerlink" title="create"></a>create</h3></li>
</ol>
</li>
<li><p>创建stu表：<code>create table stu;</code></p>
</li>
<li><p>创建stu用户：<code>create user stu;</code></p>
<h3 id="drop"><a href="#drop" class="headerlink" title="drop"></a>drop</h3></li>
<li><p>删除stu表：<code>drop table stu;</code></p>
</li>
<li><p>删除stu表中的sno字段：<code>alter table stu drop column sno;</code></p>
<h3 id="alter"><a href="#alter" class="headerlink" title="alter"></a>alter</h3></li>
<li><p>修改密码：<code>alter user zzj identified by 123456;</code></p>
</li>
<li><p>修改用户状态为锁定：<code>alter user zzj account lock;</code></p>
</li>
<li><p>修改stu表的字段sno数据类型为10位数字：<code>alter table stu modify sno number(10);</code></p>
</li>
<li><p>删除stu表中的sno字段：<code>alter table stu drop column sno;</code></p>
</li>
<li><p>birth_date字段重命名：<code>alter table stu rename column birth_date to birth;</code></p>
</li>
<li><p>loc字段增加：<code>alter table stu add loc varchar2(200);</code></p>
</li>
<li><p>这里注意增加和修改字段没有column,它们都涉及到数据类型的修改。</p>
</li>
</ol>
<h2 id="to-date函数：将字符串类型的字符串转换成"><a href="#to-date函数：将字符串类型的字符串转换成" class="headerlink" title="to_date函数：将字符串类型的字符串转换成"></a>to_date函数：将字符串类型的字符串转换成</h2><ol>
<li><code>insert into student values(4,&#39;123&#39;,&#39;男&#39;,&#39;南京&#39;，to_date(&#39;1999-04-18&#39;,&#39;YYYY-MM-DD&#39;))</code></li>
<li>YYYY:年 MM：月  DD：天 HH:时 MI:分 SS:秒</li>
<li>sysdate：系统当前时间，精确到秒。</li>
</ol>
<h2 id="改sqldeveloper默认时间格式"><a href="#改sqldeveloper默认时间格式" class="headerlink" title="改sqldeveloper默认时间格式"></a>改sqldeveloper默认时间格式</h2><p>工具-首选项-数据库-NLS-日期格式-改为<code>YYYY-MM-DD HH24:MI:SS</code></p>
<h2 id="DML语言（数据操纵语言，insert，update，delete）"><a href="#DML语言（数据操纵语言，insert，update，delete）" class="headerlink" title="DML语言（数据操纵语言，insert，update，delete）"></a>DML语言（数据操纵语言，insert，update，delete）</h2><h3 id="注意这三个指令都不需要加table"><a href="#注意这三个指令都不需要加table" class="headerlink" title="注意这三个指令都不需要加table"></a>注意这三个指令都不需要加table</h3><h3 id="insert-字段插入"><a href="#insert-字段插入" class="headerlink" title="insert 字段插入"></a>insert 字段插入</h3><ol>
<li>全字段插入：<code>insert into student values(2,&#39;zzj&#39;,&#39;男&#39;,null,sysdate);</code><ol>
<li>每一个字段值都要一一对应填写，sysdate：系统当前时间</li>
</ol>
</li>
<li>指定字段插入：<code>insert into student (stuid,stuname,gender birthdate) values(3,&#39;zzzj&#39;,&#39;男&#39;，sysdate);</code><h3 id="update-数据更新"><a href="#update-数据更新" class="headerlink" title="update 数据更新"></a>update 数据更新</h3></li>
<li>update 表名 set 字段名=修改值 where 条件<ol>
<li>修改姓名为小李的同学性别为女：<code>update student set gender=&#39;女&#39; where stuname=&#39;小李&#39;</code></li>
<li>修改性别为男的同学的stu_address为江苏: <code>update student set stu_address=&#39;江苏&#39; where gender=&#39;男&#39; </code><h3 id="delete"><a href="#delete" class="headerlink" title="delete"></a>delete</h3></li>
</ol>
</li>
<li>delete from 表名 where 条件<ol>
<li>删除id为1的学生信息：<code>delete from student where stuid=1</code></li>
<li>删除所有学生：<code>delete from stu</code></li>
</ol>
</li>
</ol>
<h2 id="数据库设计三大范式-减少冗余，提高存储效率，但牺牲查询性能"><a href="#数据库设计三大范式-减少冗余，提高存储效率，但牺牲查询性能" class="headerlink" title="数据库设计三大范式(减少冗余，提高存储效率，但牺牲查询性能)"></a>数据库设计三大范式(减少冗余，提高存储效率，但牺牲查询性能)</h2><ol>
<li>表的每一个字段不可再分（例如姓名学号不能合成一个字段）</li>
<li>字段完全依赖主键<ol>
<li>成绩表：rid  sname gender score examdate（不合理）</li>
<li>改进：成绩表：rid sno sname score examdate  学生表：sno sname gender 只需要通过sno查找到另一张表中的sname和gender</li>
</ol>
</li>
<li>字段直接依赖于主键，而不是间接依赖。<ol>
<li>如 sid sname sex schoolname schooladdress schoolphone</li>
<li>schoolname schooladdress schoolphone字段冗余，与主键相关性不大。</li>
<li>解决方案：拆分表格，学生表：sid scho_id sname sex和学校信息表：scho_id schoolname schooladdress schoolphone</li>
</ol>
</li>
</ol>
<h2 id="数据库事务"><a href="#数据库事务" class="headerlink" title="数据库事务"></a>数据库事务</h2><ol>
<li>四大特性：ACID<ol>
<li>原子性Atomicity：事务必须有原子性，数据修改时，要么全执行，要么都不执行，即不允许事务部分的完成。</li>
<li>一致性（银行取钱，扣除的金额和提取的金额必须保持一致）</li>
<li>隔离性(一个事务对数据进行操作时，其他事务不能对其操作)</li>
<li>持久性（任何事务对数据库数据操作的影响是永久的。出错了可以进行回档。）</li>
</ol>
</li>
<li>commit;事务提交，保证数据的一致性</li>
<li>rollback;事务回滚，回滚到上次进行commit提交的地方。</li>
<li>savepoint;设置存档点（保存点）<ol>
<li>savepoint A;</li>
<li>rollback to A;</li>
<li>注意savepoint A是存放在缓存中的，一旦我们执行了commit命令，缓存里的savepoint A就会被删除，此时再也无法rollback to A存档点了。</li>
</ol>
</li>
<li>DDL语言：所有操作直接提交到数据库中<ol>
<li>create drop alter</li>
</ol>
</li>
<li>DML语言：所有的操作会提交到一个日志文件中（缓存），只有当缓存被commit到数据库中，数据才会真正被存储。<ol>
<li>insert delete update</li>
</ol>
</li>
</ol>
<h2 id="单表查询语句"><a href="#单表查询语句" class="headerlink" title="单表查询语句"></a>单表查询语句</h2><h3 id="查询当前用户所有的表"><a href="#查询当前用户所有的表" class="headerlink" title="查询当前用户所有的表"></a>查询当前用户所有的表</h3><p><code>select * from all_tables;</code></p>
<h3 id="查询是否有某张表（注意必须大写）"><a href="#查询是否有某张表（注意必须大写）" class="headerlink" title="查询是否有某张表（注意必须大写）"></a>查询是否有某张表（注意必须大写）</h3><p><code>select * from all_tables where table_name=&#39;TEST&#39;;</code>  </p>
<h3 id="查询系统所有的用户"><a href="#查询系统所有的用户" class="headerlink" title="查询系统所有的用户"></a>查询系统所有的用户</h3><p><code>select * from all_users;</code>   </p>
<h3 id="查询emp表所有字段："><a href="#查询emp表所有字段：" class="headerlink" title="查询emp表所有字段："></a>查询emp表所有字段：</h3><p><code>select * from emp;</code></p>
<h3 id="查询指定字段："><a href="#查询指定字段：" class="headerlink" title="查询指定字段："></a>查询指定字段：</h3><p><code>select ename,sal from emp;</code></p>
<h3 id="别名："><a href="#别名：" class="headerlink" title="别名："></a>别名：</h3><p><code>select ename 员工姓名,sal 员工薪水 from emp;</code><br>或者：<code>select ename as 员工姓名，sal as 员工薪水 from emp;</code><br>注意：别名不会更改ename字段和sal字段</p>
<h3 id="字符拼接-："><a href="#字符拼接-：" class="headerlink" title="字符拼接||："></a>字符拼接||：</h3><pre><code>     select enme ||&#39;员工薪水是&#39;||sal from emp;
     select ename ||&#39;员工在&#39;||hiredate||&#39;入职，薪水是&#39;||sal||&#39;奖金是&#39;||nvl(comm,0) as &#39;员工入职信息&#39; from emp;
     nvl将null值改为0；
</code></pre>
<h3 id="查询结果去重："><a href="#查询结果去重：" class="headerlink" title="查询结果去重："></a>查询结果去重：</h3><p><code>select distinct (job) from emp;</code></p>
<h3 id="order-by数据排序，默认正序，倒序加上desc"><a href="#order-by数据排序，默认正序，倒序加上desc" class="headerlink" title="order by数据排序，默认正序，倒序加上desc:"></a>order by数据排序，默认正序，倒序加上desc:</h3><p><code>select enamel sal from emp order by sal asc;正序，asc可以省略  </code><br><code>select ename,sal from emp order by sal desc;倒序  </code></p>
<h3 id="多字段排序："><a href="#多字段排序：" class="headerlink" title="多字段排序："></a>多字段排序：</h3><p><code>select empno,ename,hiredate,sal from emp order by sal,empno desc;</code></p>
<h3 id="字段四则运算："><a href="#字段四则运算：" class="headerlink" title="字段四则运算：+ - * / %"></a>字段四则运算：+ - * / %</h3><p>查询所有员工的年薪：<code>select sal*12 年薪，ename from emp;</code></p>
<h3 id="where-关键字，提供筛选"><a href="#where-关键字，提供筛选" class="headerlink" title="where 关键字，提供筛选"></a>where 关键字，提供筛选</h3><p>查询薪水高于两千的员工信息：  </p>
<p><code>select * from emp where sal&gt;2000;</code>  </p>
<p>查询工资大于2000或者小于1500的员工信息：  </p>
<p><code>select * from emp where sal&gt;2000 or sal&lt;1500;</code>  </p>
<p>查询职位为MANAGER且部门号为20的员工信息：  </p>
<p><code>select * from emp where depno=20 and job=&#39;MANAGER&#39;;</code></p>
<h3 id="between-and-取范围内的值，闭区间"><a href="#between-and-取范围内的值，闭区间" class="headerlink" title="between  and  (取范围内的值，闭区间)"></a>between  and  (取范围内的值，闭区间)</h3><p>查询工资在2000到30000的员工信息：<br><code>select * from emp where sal between 2000 and 3000;注意2000和3000是可以取到的。（可用and替换）</code></p>
<h3 id="in（枚举in中的值）"><a href="#in（枚举in中的值）" class="headerlink" title="in（枚举in中的值）"></a>in（枚举in中的值）</h3><p>查询职位为salsman，analyst或manager的员工信息：<br><code>select * from emp where job in (&#39;SALSMAN&#39;,&#39;ANALYST&#39;,&#39;MANAGER&#39;);（可用or替换）</code></p>
<h3 id="like模糊查询"><a href="#like模糊查询" class="headerlink" title="like模糊查询"></a>like模糊查询</h3><p>%通配符,匹配任意长度字符串，匹配0个，1个或者n个。<br>查询带s：<code>select * from emp where ename like &#39;%s%&#39;</code>  </p>
<p>查询以s开头：<code>select * from emp where ename like &#39;s%&#39;</code>  </p>
<p>查询以s结尾：<code>select * from emp where ename like &#39;%s&#39;</code>  </p>
<p>查询第二个字符为A：<code>select * from emp where ename like &#39;_A%&#39;</code>  </p>
<p>查询第二个字符为A,倒数第二个为M：<code>select * from emp where ename like &#39;_A%M_&#39;</code>  </p>
<h2 id="字符串函数"><a href="#字符串函数" class="headerlink" title="字符串函数"></a>字符串函数</h2><h3 id="获取字符串长度"><a href="#获取字符串长度" class="headerlink" title="获取字符串长度"></a>获取字符串长度</h3><p><code>select substr(&#39;www.baidu.com&#39;,0,length(&#39;www.baidu.com&#39;)-4) from dual;</code></p>
<h2 id="替换"><a href="#替换" class="headerlink" title="替换"></a>替换</h2><p><code>select replace(&#39;ename&#39;,&#39;e&#39;,&#39;E&#39;)from dual;</code></p>
<h3 id="首字母大写"><a href="#首字母大写" class="headerlink" title="首字母大写"></a>首字母大写</h3><p><code>select initcap(ename) from dual;</code></p>
<h3 id="强制小写"><a href="#强制小写" class="headerlink" title="强制小写"></a>强制小写</h3><p><code>select lower(ename) from dual;</code></p>
<h3 id="强制大写"><a href="#强制大写" class="headerlink" title="强制大写"></a>强制大写</h3><p><code>select upper(&#39;abcdefg&#39;) from dual;</code></p>
<h3 id="过滤首尾空格"><a href="#过滤首尾空格" class="headerlink" title="过滤首尾空格"></a>过滤首尾空格</h3><p><code>select trim(&#39;   www efwer ew    &#39;) from dual;</code></p>
<h3 id="左裁剪-第一个参数中去掉zyz来显示，zyz必须位于最左边"><a href="#左裁剪-第一个参数中去掉zyz来显示，zyz必须位于最左边" class="headerlink" title="左裁剪(第一个参数中去掉zyz来显示，zyz必须位于最左边)"></a>左裁剪(第一个参数中去掉zyz来显示，zyz必须位于最左边)</h3><p><code>select ltrim (&#39;zyz is handsome&#39;,&#39;zyz&#39;) from dual;</code></p>
<h3 id="右裁剪-第一个参数中去掉handsome来显示，handsome必须位于最右边"><a href="#右裁剪-第一个参数中去掉handsome来显示，handsome必须位于最右边" class="headerlink" title="右裁剪(第一个参数中去掉handsome来显示，handsome必须位于最右边)"></a>右裁剪(第一个参数中去掉handsome来显示，handsome必须位于最右边)</h3><p><code>select rtrim (&#39;zyz is handsome&#39;,&#39;handsome&#39;) from dual;</code></p>
<h3 id="按字符翻译-略"><a href="#按字符翻译-略" class="headerlink" title="按字符翻译(略)"></a>按字符翻译(略)</h3><p><code>select translate（&#39;jack&#39;,&#39;abcdefg&#39;,&#39;23456&#39;）from dual;</code></p>
<h3 id="查找字符串位置（略）"><a href="#查找字符串位置（略）" class="headerlink" title="查找字符串位置（略）"></a>查找字符串位置（略）</h3><p><code>select instr(&#39;字符串函数&#39;,&#39;函&#39;) from dual;</code></p>
<h3 id="截取字符串（初识下标为1）"><a href="#截取字符串（初识下标为1）" class="headerlink" title="截取字符串（初识下标为1）"></a>截取字符串（初识下标为1）</h3><p>从下标为3截取三位：<code>select substr(&quot;l like study&quot;, 3,3) from dual;</code></p>
<h3 id="字符串连接-类似于-区别concat只能有两个参数"><a href="#字符串连接-类似于-区别concat只能有两个参数" class="headerlink" title="字符串连接(类似于||,区别concat只能有两个参数)"></a>字符串连接(类似于||,区别concat只能有两个参数)</h3><p><code>select concat(&#39;字符串&#39;,&#39;函数&#39;) from dual;</code></p>
<h2 id="数学函数"><a href="#数学函数" class="headerlink" title="数学函数"></a>数学函数</h2><ol>
<li>取绝对值<ol>
<li><code>select abs(-1) from dual;</code></li>
</ol>
</li>
<li>ceil向上取整<ol>
<li><code>select ceil(3.9) from dual;</code></li>
</ol>
</li>
<li>floor向下取整<ol>
<li><code>select floor(2.3) from dual;</code></li>
</ol>
</li>
<li>power求幂<ol>
<li><code>select power(2,3) from dual;</code></li>
</ol>
</li>
<li>mod取余<ol>
<li><code>select mod(10,3) from dual;</code></li>
</ol>
</li>
<li>round四舍五入<ol>
<li><code>select round (1.5) from dual;</code></li>
</ol>
</li>
<li>trunc截取小数位置<ol>
<li><code>select trunc(1.23456789,3) from dual;结果1.234</code></li>
</ol>
</li>
</ol>
<h2 id="日期数据"><a href="#日期数据" class="headerlink" title="日期数据"></a>日期数据</h2><ol>
<li>查询当前系统的日期sysdate<ol>
<li><code>select sysdate from dual;</code></li>
</ol>
</li>
<li>查询两个日期之间的月份间隔数montns_between<ol>
<li><code>select floor(month_between(sysdate,&#39;2010-05-10&#39;)) from dual;</code></li>
</ol>
</li>
<li>增减指定月数：add_months<ol>
<li><code>select add_months(sysdate,-6) from dual;</code></li>
</ol>
</li>
<li>返回指定日期后的下一个星期 next_day<ol>
<li>查下个星期日是几号：<code>select net_day(sysdate,&#39;星期日&#39;) from dual;</code></li>
</ol>
</li>
<li>返回指定日期所在月份的最后一天的日期：last_day<ol>
<li><code>select last_day(sysdate) from dual;</code></li>
<li><code>select last_day(&#39;2020-05-20&#39;) from dual;</code></li>
</ol>
</li>
<li>日期格式转化为字符串格式 to_char<ol>
<li><code>select to_char(sysdate) from dual;</code></li>
<li>格式：<code>YYYY-MM-DD hh:mm:ss</code>   24小时制：hh24</li>
<li>指定日期格式： <code>select to_char(sysdate,&#39;YYYY-MM-DD&#39;) from dual;</code></li>
</ol>
</li>
<li>字符串转化为日期格式to_date<ol>
<li><code>select to_date(&#39;2020-02-20&#39;,&#39;YYYY-MM-DD&#39;) from dual;</code></li>
</ol>
</li>
<li>日期的四舍五入<ol>
<li><code>select round(sysdate,&#39;YEAR&#39;) from dual;</code></li>
</ol>
</li>
<li>日期截取<ol>
<li> 从日期中截取天：<code>select trunc(sysdate,&#39;DD&#39;) from dual;</code></li>
</ol>
</li>
<li>注意：<ol>
<li> round和trunc的第一个参数必须为日期函数</li>
<li> 例：<code>select round(to_date(&#39;2020-05-20&#39;,&#39;YYYY-MM-DD&#39;)) from dual;</code></li>
</ol>
</li>
</ol>
<h2 id="常用聚合函数（max-min-avg-sum-count）"><a href="#常用聚合函数（max-min-avg-sum-count）" class="headerlink" title="常用聚合函数（max,min,avg,sum,count）"></a>常用聚合函数（max,min,avg,sum,count）</h2><ol>
<li>max:返回指定字段的最大值<ol>
<li><code>select max(sal) from emp;</code></li>
</ol>
</li>
<li>min:返回指定字段的最小值<ol>
<li><code>select min(sal) from emp;</code></li>
</ol>
</li>
<li>avg:返回指定字段的平均值<ol>
<li><code>select trunc(avg(sal)) from emp;</code></li>
<li>trunc用来去除小数部分</li>
</ol>
</li>
<li>count<ol>
<li>count(*):查看当前表有多少条记录<ol>
<li><code>select * from emp;</code> 大型数据不要使用，很慢。</li>
</ol>
</li>
<li>count(1):查看当前表有多少条记录<ol>
<li><code>select count(1) from emp;</code></li>
</ol>
</li>
<li>两者比较：<ol>
<li>count(*)查询的是当前表的所有字段</li>
<li>count(1)查询的是该字段的所有非空值的数量，相比而言效率更高。</li>
</ol>
</li>
</ol>
</li>
</ol>
<h2 id="单表查询演练（7，9，10-14-15）"><a href="#单表查询演练（7，9，10-14-15）" class="headerlink" title="单表查询演练（7，9，10,14,15）"></a>单表查询演练（7，9，10,14,15）</h2><p><img src="https://imgconvert.csdnimg.cn/aHR0cHM6Ly96emotMTMwMDQwODQ5My5jb3MuYXAtY2hlbmdkdS5teXFjbG91ZC5jb20vJUU1JTlCJUJFJUU1JUJBJThBLzQ5LnBuZw?x-oss-process=image/format,png">  </p>
<ol>
<li><code>select * from emp where sal=1250;</code></li>
<li><code>select * from emp where job=&#39;CLERK&#39;;</code></li>
<li><code>select ename,job from emp where sal&gt;1250;</code></li>
<li><code>select * from emp where sal&gt;=2000;</code></li>
<li><code>select * from emp where sal&lt;=2000;</code></li>
<li><code>select * from emp where sal!=1500;</code></li>
<li>方法1：将入职日期转化为字符串，再与1981比较<code>select * from emp where to_char(hiredate,&#39;YYYY&#39;)&gt;1981;</code><ol>
<li>方法2：将入职日期的年份截取，再将1981转化为日期，两者进行比较<code>select * from emp where trunc(hiredate,&#39;YEAR&#39;)&gt;to_date(1981,&#39;YYYY&#39;);</code></li>
<li>方法3：<code>select * from emp where hiredate&gt;&#39;1981-12-31&#39;;</code><ol>
<li> 注意：1981类型为字符串。’1981-12-31’类型为日期,但必须加上引号，等同于to_date(hiredate,’YYYY-MM-DD’)。</li>
</ol>
</li>
<li>方法4：<code>select * from emp where hiredate&gt;to_date(&#39;1981-12-31&#39;,YYYY-MM-DD);</code></li>
<li>一般使用方法3和方法1<h2 id=""><a href="#" class="headerlink" title=""></a></h2></li>
</ol>
</li>
<li><code>select * from emp where deptno=30;</code></li>
<li><code>select ename,empno,deptno from emp where job=&#39;CLERK&#39;;</code></li>
<li><code>select * from emp where comm&gt;sal;</code></li>
<li><code>select * from emp where commm&gt;0.6*sal;</code></li>
<li><code>select * from emp where deptno=10 and job=&#39;MANAGER&#39; or deptno=20 and job=&#39;CLERK&#39;;</code></li>
<li><code>select * from emp where deptno=10 and job=&#39;MANAGER&#39; or deptno=20 and job=&#39;CLERK&#39; or job not in (&#39;MANAGER&#39;,&#39;CLERK&#39;) and sal&gt;=2000;</code></li>
<li><code>select distinct(job) from emp where comm is not null;</code>这里注意不要少了is</li>
<li><code>select * from emp where comm is null or comm is not null and comm&gt;100;</code></li>
<li><code>select * from emp where hiredate=last_day(hiredate)-2;</code><ol>
<li> 重点：日期与日期之间可以相互运算，比较</li>
<li> 这里的hiredate和last_day的格式都是YYYY-MM-DD hh:mm:ss</li>
<li> -2就是指减去两天，不需要将hiredate和last_day截取成DAY格式再与2相减。</li>
</ol>
</li>
<li><code>select * from emp where floor(month_between(sysdate,hiredate)/12)&gt;12;</code><ol>
<li>方法2：<code>select * from emp where sysdate-12&gt;hiredate;</code><ol>
<li> 注意不能用<code>select * from emp where sysdate-hiredate&gt;12;</code>,因为sysdate-hiredate结果会自动转化成以天为单位</li>
</ol>
</li>
<li>方法3：<code>select * from emp where to_char(sysdate,&#39;YYYY&#39;)-to_char(hiredate,&#39;YYYY&#39;)&gt;12;</code><ol>
<li>不能写成：<code>select * from emp where to_char(sysdate-hiredate,&#39;YYYY&#39;)&gt;12;</code>原因同上</li>
</ol>
</li>
</ol>
</li>
<li><code>select initcap(ename) from emp;</code></li>
<li><code>select * from emp where length(ename)=5;</code><ol>
<li> 或者<code>select * from emp where ename like &#39;_____&#39;</code></li>
<li> length函数获取字段值长度</li>
</ol>
</li>
<li><code>select * from emp where ename not like &#39;%R%&#39;;</code></li>
<li><code>select substr(ename,1,3) from emp;</code></li>
<li><code>select translate(ename,&#39;A&#39;,&#39;a&#39;) from emp;</code><ol>
<li> 或者<code>select replace(ename,&#39;A&#39;,&#39;a&#39;) from emp;</code></li>
<li> 重点记忆</li>
</ol>
</li>
<li><code>select ename,hiredate from emp where trunc(sysdate,&#39;YYYY&#39;)-10&gt;hiredate;</code></li>
<li><code>select * from emp order by ename;</code></li>
<li><code>select ename,hiredate from emp order by hiredate;</code></li>
<li><code>select ename,job,sal from emp order by job desc,sal;</code></li>
<li><code>select ename,to_char(hiredate,&#39;YYYY&#39;)year,to_cahr(hiredate,&#39;MM&#39;)month order by month,year;</code></li>
<li><code>select ename,round(sal/30) from emp;</code></li>
<li><code>select * from emp where to_char(hiredate,&#39;MM&#39;)=02;</code></li>
<li><code>select floor(sysdate-hiredate) from emp;</code><ol>
<li> 注意：<code>select sysdate from emp;</code>结果格式为YYYY–MM-DD hh:mm:ss</li>
<li> 而sysdate-hiredate会默认转换为天数。</li>
</ol>
</li>
<li><code>select ename from emp where ename like &#39;%A%&#39;;</code></li>
</ol>
<h2 id="分组查询-分组：将具有相同指定字段值的记录归纳为同一组"><a href="#分组查询-分组：将具有相同指定字段值的记录归纳为同一组" class="headerlink" title="分组查询(分组：将具有相同指定字段值的记录归纳为同一组)"></a>分组查询(分组：将具有相同指定字段值的记录归纳为同一组)</h2><ol>
<li>查询各个部门的平均薪水sal：<ol>
<li>错误方法<br><code>select avg(sal),deptno from emp where deptno=10;</code><br><code>select avg(sal),deptno from emp where deptno=20;</code><br><code>select avg(sal),deptno from emp where deptno=30;</code>  </li>
</ol>
– 出错，既指定了聚合函数(avg(sal))，又有其他字段(deptno)，就必须group by ，否则语法不通过<ol start="2">
<li>方法2：分组查询：<br><code>select avg(sal),deptno from emp group by deptno;</code><br><code>select avg(sal),deptno,ename from emp group by deptno;</code>– 出错，不是group by 表达式,group by 语句只能查询五中聚合函数（count min max avg abs）以及被分组字段本身.</li>
</ol>
</li>
<li>group by 适用场景  <ol>
<li>既指定了聚合函数(avg(sal))，又有唯一其他字段(deptno)，就必须group by</li>
<li>数据被分组之后，只保留组数据，不保留用户分组之间   </li>
<li>group by 语句只能查询五中聚合函数（可以有多个：count min max avg abs）以及被分组字段本身</li>
<li>查询各个职位的平均sal,最高sal以及职业名称<ol>
<li><code>select avg(sal),max(sal),job from emp group by job;</code></li>
</ol>
</li>
<li>having 条件筛选，做分组之后的数据筛选，而where筛选的是普通的数据</li>
<li>查询所有平均薪水大于2000的部门和薪水<ol>
<li><code>select deptno,avg(sal) from emp group by deptno having avg(sal)&gt;2000;</code><h2 id="sql语句执行顺序"><a href="#sql语句执行顺序" class="headerlink" title="sql语句执行顺序"></a>sql语句执行顺序</h2></li>
</ol>
</li>
</ol>
</li>
<li>from 表名 读取指定表数据</li>
<li>join</li>
<li>on</li>
<li>where 条件 做第一次数据筛选</li>
<li>groupby 分组 对数据分组</li>
<li>avg,sum</li>
<li>having条件 对分组之后的数据进行筛选</li>
<li>select 从前面筛选好的数据中，选择部分字段进行显示</li>
<li>distinct</li>
<li>order by 按照指定字段进行排序，order在设计的时候放在最后运行避免进行大量的冗余计算。（筛选完后再排序）<h2 id="rownum-每个表都有一个隐藏的字段rownum，下标从1开始递增"><a href="#rownum-每个表都有一个隐藏的字段rownum，下标从1开始递增" class="headerlink" title="rownum(每个表都有一个隐藏的字段rownum，下标从1开始递增)"></a>rownum(每个表都有一个隐藏的字段rownum，下标从1开始递增)</h2></li>
<li>查询整张emp表和rownum<ol>
<li><code>elect rownum,e.* from emp e; </code></li>
<li>–e是emp别名，e.*指emp所有字段</li>
</ol>
</li>
<li>查询emp表前10行记录<ol>
<li><code>select * from emp where rownum&lt;11;</code></li>
</ol>
</li>
<li>查询emp表的第5到10行<ol>
<li><code>select * from emp where rownum between 5 and 10;</code>（错误）</li>
<li>rownum最小值为1，从1开始匹配，rownum只要一次条件不满足，后续所有条件都不匹配，第一次1不满足，后续就不匹配了。即rownum不能用&gt;</li>
</ol>
</li>
<li>查询公司中薪水前十高的员工<ol>
<li><code>select * from emp order by sal where rownum &lt;11;</code> – 不能运行，顺序不对，order by应该写在最后</li>
<li><code>select * from emp where rownum&lt;11 order by sal desc;</code> – 运行结果不对，先截取再排序了</li>
<li>如何做到先排序再截取，同时保证order by 在最后执行？（子查询）<ol>
<li><code>select * from (select * from emp order by sal desc)a where rownum&lt;11</code></li>
</ol>
</li>
</ol>
</li>
<li>查询公司中薪水第五到第十高的员工<ol>
<li><code>select * from (select * from emp order by sal desc)where rownum&lt;11 and rownum&gt;4</code>错,rownum不能用大于</li>
<li>如果我们利用子查询使rownum以普通字段的方式显示在我们的表中<br>字段别名<br>子查询  </li>
<li>```<br>select * from<br>(select rownum r,a.* from<br>(select * from emp order by sal desc)a)<br>where r between 5 and 10;<figure class="highlight plain"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br><span class="line">6</span><br><span class="line">7</span><br><span class="line">8</span><br><span class="line">9</span><br><span class="line">10</span><br></pre></td><td class="code"><pre><span class="line">6. 子查询 使用某一个sql语句的运行结果作为另一个sql语句的执行条件,也可以作为其他查询语句的数据来源</span><br><span class="line">7. 查询出所有薪水比SMITH高的员工信息</span><br><span class="line">   </span><br><span class="line">   1. select * from emp where sal&gt;(select sal from emp where ename&#x3D;&#39;SMITH&#39;);</span><br><span class="line"></span><br><span class="line"></span><br><span class="line">## rowid 相当于每一行数据的身份证,用来去除重复记录（区别于distinct）</span><br><span class="line">1. &#96;select rowid,e.* from emp e;&#96;</span><br><span class="line"></span><br><span class="line">2. 创建一个test表</span><br></pre></td></tr></table></figure>
create table test(<br>tid number,<br>tname varchar2(20),<br>tsex varchar2(20)<br>)<br>insert into test values (1,’小猪’,’男’);<br>insert into test values (2,’小驴’,’女’);<br>insert into test values (3,’小鸡’,’男’);<br>insert into test values (1,’小猪’,’男’);<br>insert into test values (2,’小驴’,’女’);<br>insert into test values (3,’小鸡’,’男’);<br>insert into test values (1,’小猪’,’男’);<br>insert into test values (2,’小驴’,’女’);<br>insert into test values (3,’小鸡’,’男’);<figure class="highlight plain"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br><span class="line">6</span><br><span class="line">7</span><br><span class="line">8</span><br><span class="line">9</span><br></pre></td><td class="code"><pre><span class="line">-- 查询所有信息（去重）注意distinct只能针对某一个字段去重</span><br><span class="line"></span><br><span class="line">3. 结合rowid+分组去做,比大小的时候，数字比的是值，字符比的是ASCLL码值  </span><br><span class="line">   1. &#96;select tid from test group by tid;&#96;--将重复的数据划分为一组</span><br><span class="line">   2. &#96;&#96;&#96;</span><br><span class="line">      select * from test where rowid in</span><br><span class="line">      (select max (rowid)from test group by tid)      -- 取出各组数据中max（rowid）</span><br><span class="line">      order by tid;</span><br><span class="line">      commit;</span><br></pre></td></tr></table></figure></li>
<li><code>select * from test where tid in(select distinct(tid) from test); </code>              – 不能去重，因为tid不是唯一的</li>
</ol>
</li>
</ol>
<h2 id="什么是笛卡尔积"><a href="#什么是笛卡尔积" class="headerlink" title="什么是笛卡尔积"></a>什么是笛卡尔积</h2><ol>
<li><p>{a,b} {c,d,e}<br>ac ad ae bc bd be</p>
</li>
<li><p>表和表之间的笛卡尔积:<br>以一行数据作为笛卡尔级的运算单位<br>最终两张表的笛卡尔积，会将两个表的字段和数据一起做乘积<br>例:<br>emp 14条记录  字段值：<code>empno ename job mgr hiredate sal comm deptno  </code><br>dept 4条记录  字段值：<code>deptno dname loc</code><br>两张表做笛卡尔积：<code>select * from emp,dept;</code><br>结果包含56条记录，表的字段值为<code>deptno empno ename job mgr hiredate sal comm  dname loc deptno_1</code>  </p>
</li>
</ol>
<p>– 笛卡尔积中会包含所有匹配的可能<br>– 笛卡尔积中会包含两个表的所有字段  </p>
<ol start="3">
<li>查询姓名为SMITH的员工的部门所在地（loc）（相同字段loc）  <ol>
<li><code>select loc from dept where deptno=(select deptno from emp where ename=&#39;SMITH&#39;)</code>（先在emp表中通过姓名去获取deptno，再通过deptno获取到对应的loc）  </li>
<li>缺点：只适用于查询的字段都在一张表中  </li>
</ol>
</li>
<li>查询姓名为SMITH的员工的sal,loc(sal,loc不在一张表)  <ol>
<li>方法2：<code>select e.sal,d.loc from emp e,dept d where e.ename=&#39;SMITH&#39;</code> 有四条数据，因为笛卡尔积会造成数据冗余<br><code>select * from emp,dept where ename=&#39;SMITH&#39;; </code>发现除了第二条数据，其他三条DEPTNO！=DEPTNO_1<br>正确改法：<code>select e.sal,d.loc from emp e,dept d where e.deptno=d.deptno and e.ename=&#39;SMITH&#39;;</code>  </li>
</ol>
</li>
<li>内连接查询中的等值连接:<code>select * from emp e,dept d where e.deptno=d.deptno;</code></li>
<li>内连接的不等值连接工<ol>
<li>查询员工的编号，员工姓名，资，工资等级：</li>
<li><code>select e.empno, e.ename,e.sal,s.grade from emp e,salgrade s where e.sal between s.losal and s.hisal</code></li>
</ol>
</li>
<li>不管是等值连接还是不等值连接都是内连接查询</li>
<li>自连接(笛卡尔积的对象是自己)<ol>
<li>查询员工编号，员工姓名，主管编号，主管姓名<ol>
<li><code>select e.empno,e.ename,m.empno,m.ename from emp e, emp m where e.mgr=m.empno;</code></li>
<li>内连接的特点：<br>左右两张表满足条件匹配的才会查出来，没有匹配不会查出来（即不满足where条件的）</li>
</ol>
</li>
</ol>
</li>
<li>外连接查询<ol>
<li>左外链接（以左表为基准，坐标中所有内容都要查出来，不管是否匹配，写法：在where后面的=右边加上（+））<ol>
<li>查询员工编号，员工姓名，部门编号，部门名称（没有员工的部门也要查出来）<ol>
<li> <code>select * from dept d, emp e where d.deptno=e.deptno(+)</code></li>
</ol>
</li>
<li>查询员工编号，员工姓名,主管编号，主管姓名（没有主管的员工也要查出来）<ol>
<li> <code>select * from emp e, emp m where e.mgr=m.empno(+)</code></li>
</ol>
</li>
</ol>
</li>
<li>右外链接（以右表为基准，坐标中所有内容都要查出来，不管是否匹配，写法：在where后面的=左边加上（+））<ol>
<li>查询员工编号，员工姓名，部门编号，部门名称（没有员工的部门的也要查出来）<ol>
<li><code>select * from emp e, dept d where e.deptno(+)=d.deptno;</code></li>
<li>查询员工编号，员工姓名,主管编号，主管姓名（没有主管的员工也要查出来）<ol>
<li><code>select * from  emp m,emp e where m.empno(+)=e.mgr;</code></li>
</ol>
</li>
</ol>
</li>
</ol>
</li>
</ol>
</li>
</ol>
<h2 id="多表查询-sql99（增加了SQL92没有的关键字-可读性增强）"><a href="#多表查询-sql99（增加了SQL92没有的关键字-可读性增强）" class="headerlink" title="多表查询 sql99（增加了SQL92没有的关键字 可读性增强）"></a>多表查询 sql99（增加了SQL92没有的关键字 可读性增强）</h2><ol>
<li>笛卡尔积查询（cross join替代，）<ol>
<li><code>select* from emp cross join dept;</code></li>
</ol>
</li>
<li>内连接（inner join或者join 替代逗号  on替代where）</li>
<li>rownum连接（natural join ，92没有）<ol>
<li><code>select * from emp natural join dept;</code>将两张表相同的字段做等值匹配</li>
</ol>
</li>
<li>using(了解，效果等同于自然连接)<ol>
<li><code>select * from emp join dept using(deptno);</code>将括号里的字段做等值匹配  </li>
</ol>
</li>
<li>等值连接  on<ol>
<li><code>select e.empno,e.ename,d.deptno,d.dname from emp e inner join dept d on e.deptno=d.deptno;</code>  </li>
</ol>
</li>
<li>不等值连接 on between and <ol>
<li><code>select e.empno,e.ename,e.sal,s.grade from empe inner join salgrade s on e.sal between s.losal and s.hisal;</code>   </li>
</ol>
</li>
<li>自连接(inner join 或者 join)<ol>
<li><code>select e.empno,e.ename,m.empno,m.ename from emp e inner join emp m on e.mgr=m.empno;</code> </li>
</ol>
</li>
<li>外连接（outer join 替代逗号，不需要（+），on替代where）<ol>
<li>左外连接（left outer join）<ol>
<li>```<br>select d.deptno,d.dname,e.empno,e.ename<br>from dept d left outer join emp e<br>on d.deptno=e.deptno;<figure class="highlight plain"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br></pre></td><td class="code"><pre><span class="line">2. 右外连接（right outer join）</span><br><span class="line">   1. &#96;&#96;&#96;</span><br><span class="line">      select e.empno,e.ename,d.deptno,d.dname</span><br><span class="line">      from dept d right outer join emp e</span><br><span class="line">      on e.deptno&#x3D;p.deptno;</span><br></pre></td></tr></table></figure></li>
</ol>
</li>
<li>满外连接（full outer join）<ol>
<li>```<br>select e.empno,e.ename,d.deptno,d.dname<br>from dept d full outer join emp e<br>on e.deptno=p.deptno;<figure class="highlight plain"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br></pre></td><td class="code"><pre><span class="line">9. 查询工资在2000以上的员工的姓名，部门名称</span><br><span class="line">   </span><br><span class="line">   1.  &#96;select e.ename,d.dname from emp inner join dept on e.deptno&#x3D;d.deptno where e.sal&gt;2000;&#96;</span><br><span class="line"></span><br><span class="line">## sql99相较于sql92</span><br></pre></td></tr></table></figure></li>
</ol>
</li>
</ol>
</li>
</ol>
<p>– 92写法<br>– 一次性将所有的关联的表做笛卡儿积 再去筛选<br>select e.empno,e.ename,d.deptno,d.dname,s.grade<br>    from emp e,dept d,salgrade s  – 14  4 5  280<br>    where e.deptno=d.deptno<br>    and e.sal between s.losal and s.hisal;<br>– 99写法<br>– 每笛卡儿积一次,就筛选一次,筛选之后的数据再进行笛卡儿积<br>select e.empno,e.ename,d.deptno,d.dname,s.grade<br>    from emp e inner join dept d  – 56<br>    on e.deptno=d.deptno  – 14条<br>    inner join salgrade s  – 14*5   70条<br>    on e.sal between s.losal and s.hisal; </p>
<figure class="highlight plain"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br><span class="line">6</span><br><span class="line">7</span><br><span class="line">8</span><br><span class="line">9</span><br><span class="line">10</span><br><span class="line">11</span><br><span class="line">12</span><br><span class="line">13</span><br><span class="line">14</span><br><span class="line">15</span><br><span class="line">16</span><br><span class="line">17</span><br><span class="line">18</span><br><span class="line">19</span><br><span class="line">20</span><br><span class="line">21</span><br><span class="line">22</span><br><span class="line">23</span><br><span class="line">24</span><br><span class="line">25</span><br><span class="line">26</span><br><span class="line">27</span><br><span class="line">28</span><br><span class="line">29</span><br><span class="line">30</span><br><span class="line">31</span><br><span class="line">32</span><br><span class="line">33</span><br><span class="line">34</span><br><span class="line">35</span><br><span class="line">36</span><br><span class="line">37</span><br><span class="line">38</span><br><span class="line">39</span><br><span class="line">40</span><br><span class="line">41</span><br><span class="line">42</span><br><span class="line">43</span><br><span class="line">44</span><br><span class="line">45</span><br><span class="line">46</span><br><span class="line">47</span><br><span class="line">48</span><br><span class="line">49</span><br><span class="line">50</span><br><span class="line">51</span><br><span class="line">52</span><br><span class="line">53</span><br><span class="line">54</span><br><span class="line">55</span><br><span class="line">56</span><br><span class="line">57</span><br><span class="line">58</span><br><span class="line">59</span><br><span class="line">60</span><br><span class="line">61</span><br><span class="line">62</span><br><span class="line">63</span><br><span class="line">64</span><br><span class="line">65</span><br><span class="line">66</span><br><span class="line">67</span><br><span class="line">68</span><br><span class="line">69</span><br><span class="line">70</span><br><span class="line">71</span><br><span class="line">72</span><br><span class="line">73</span><br><span class="line">74</span><br><span class="line">75</span><br><span class="line">76</span><br><span class="line">77</span><br><span class="line">78</span><br><span class="line">79</span><br><span class="line">80</span><br><span class="line">81</span><br><span class="line">82</span><br><span class="line">83</span><br><span class="line">84</span><br><span class="line">85</span><br><span class="line">86</span><br><span class="line">87</span><br></pre></td><td class="code"><pre><span class="line"></span><br><span class="line">## 多表查询演练</span><br><span class="line">1. 列出所有雇员的姓名及其直接上级的姓名</span><br><span class="line">select e,ename,m.ename from emp e join emp m on e.mgr&#x3D;m.empno; </span><br><span class="line">2. 列出部门名称和这些部门的雇员,同时列出那些没有雇员的部门;</span><br><span class="line">select d,dname,e.* from dept d left outer join emp e on d.deptno&#x3D;e.deptno </span><br><span class="line">3. 显示所有部门在&quot;NEW YORK&quot;(dept表 loc字段)的员工姓名</span><br><span class="line">select ename from emp where deptno &#x3D; </span><br><span class="line">(select deptno from dept where loc&#x3D;&#39;NEWYORK&#39;)</span><br><span class="line"></span><br><span class="line">4. 显示员工&quot;SMITH&quot;的姓名，部门名称</span><br><span class="line">select e.ename,d.dname from emp e inner join dept d on e.deptno&#x3D;d.deptno;</span><br><span class="line">5. 显示员工姓名，部门名称，工资，工资级别(salgrade表 grade字段),要求工资级别大于4级</span><br><span class="line"></span><br><span class="line">select e.ename,d.dname,e.sal,s.grade</span><br><span class="line">from emp e inner join dept d</span><br><span class="line">on e.deptno&#x3D;d.deptno </span><br><span class="line">inner join salgrade s </span><br><span class="line">on e.sal between s.losal and s.hisal</span><br><span class="line">where s.grade&gt;4;</span><br><span class="line"></span><br><span class="line">6. 显示员工&quot;KING&quot;和&quot;FORD&quot;管理的员工姓名及其经理姓名</span><br><span class="line">select e.ename,m.ename</span><br><span class="line">from emp e inner join emp m on e.mgr&#x3D;m.empno</span><br><span class="line">where m.ename in(&#39;KING&#39;,&#39;FORD&#39;);</span><br><span class="line"></span><br><span class="line"></span><br><span class="line"></span><br><span class="line">7. 显示员工名，参加工作时间，经理名，参加工作时间：参加工作时间比他的经理早</span><br><span class="line">select e.ename,e.hiredate,m.ename,m.hiredate</span><br><span class="line">from emp e inner join emp m </span><br><span class="line">on e.mgr&#x3D;m.empno</span><br><span class="line">where e.hiredate&lt; m.hiredate</span><br><span class="line"></span><br><span class="line">8. 求出部门名称中，带&#39;S&#39;字符的部门员工的工资总和 、部门人数</span><br><span class="line">-- 模糊查询</span><br><span class="line">-- 按照部门进行分组查询</span><br><span class="line">select sum(sal),count(*)</span><br><span class="line">from emp e where e.deptno in</span><br><span class="line">(select d.deptno from dept d where d.dname like &#39;%S%&#39;)</span><br><span class="line">group by e.deptno</span><br><span class="line"></span><br><span class="line"></span><br><span class="line">9. 列出所有员工的年工资，按年薪从低到高排序</span><br><span class="line">       select e.*,(sal+nvl(comm,0))*12 nianxin </span><br><span class="line">       from emp e order by nianxin;</span><br><span class="line">10. 列出各种工作的最低工资以及从事此工作的雇员姓名</span><br><span class="line">select e.ename,e.sal,e.job </span><br><span class="line">from emp e,</span><br><span class="line">(select min(sal) losal,job from emp group by job)m</span><br><span class="line">where e.sal&#x3D;m.losal and e.job&#x3D;m.job -- 员工工资等于最低工资 员工工作&#x3D;最低工资对应工作</span><br><span class="line"></span><br><span class="line">11. 列出所有部门的详细信息和部门人数</span><br><span class="line">select d.*,m.c </span><br><span class="line">from dept d left outer join (select count(*) c,deptno from emp group by deptno) m</span><br><span class="line">on d.deptno &#x3D; m.deptno;</span><br><span class="line">12. 列出所有员工的姓名、部门名称和工资</span><br><span class="line">select e.ename,d.dname,e.sal from emp e inner join dept d on e.deptno&#x3D;d.deptno;</span><br><span class="line">13. 查询员工信息： 员工的名字 老板的名字  </span><br><span class="line">select ename,</span><br><span class="line">(select ename from emp where mgr is null) boss</span><br><span class="line">from emp;</span><br><span class="line">14. 要求查询出每一个雇员的编号、姓名、工资、部门名称、工资在所在公司的工资等级 </span><br><span class="line">select e.empno,e.ename,e.sal,d.dname,s.grade</span><br><span class="line">from emp e inner join dept d on e.deptno &#x3D; d.deptno </span><br><span class="line">inner join salgrade s on e.sal between s.losal and s.hisal</span><br><span class="line">15. 按照职位分组，求出每个职位的最高工资、最低工资以及平均工资 </span><br><span class="line">select max(sal),min(sal),floor(avg(sal)),job from emp group by job;</span><br><span class="line">16. 统计平均工资大于2000的部门的详细信息</span><br><span class="line"></span><br><span class="line">select * from dept where deptno in</span><br><span class="line">(select deptno from emp group by deptno having avg(sal)&gt;2000)</span><br><span class="line"></span><br><span class="line"></span><br><span class="line">## in和exists区别</span><br><span class="line">exists是相关子查询，以外部查询为主。</span><br><span class="line"></span><br><span class="line"></span><br><span class="line">## 数据库对象（理解为数据库中的个体）</span><br><span class="line">- 包括：序列，视图，索引，表空间，同义词</span><br><span class="line">- 表也是数据库对象</span><br><span class="line">### 同义词</span><br><span class="line">- 查询语句中，别名是为表中的字段创建的</span><br><span class="line">- 而同义词是一种特殊的别名，是基于其他数据库对象使用的。</span><br><span class="line">- 私有同义词和公有同义词</span><br><span class="line">- 共有同义词可被所有数据库用户访问，而私有同义词只能被当前用户访问。</span><br><span class="line">- create [or replace] [public] synonym 同义词名称 for 对象名称</span><br></pre></td></tr></table></figure>
<p>演示：在用户B17040125下创建一个scott用户的emp表的同义词，前提是B17040125被赋予访问scott用户的权限。<br>1.切换到管理员用户</p>
<p>2.创建用户并赋予权限<br>create user b17040125 identified by 123456;<br>grant connect,resource to b17040125;</p>
<p>3.创建同义词同样需要权限,赋予用户B17040125创建同义词的权限<br>grant create public synonym to b17040125; </p>
<p>4.切换到b17040125用户</p>
<p>5.创建同义词<br>create or replace public synonym MyDept for scott.dept;<br>create or replace synonym MyEmp for scott.emp;</p>
<p>6.在b17040125用户下执行：<br>select * from MyDept;<br>select * from MyEmp;<br>报错：表或视图不存在</p>
<p>– 权限？<br>– 管理员用户是否可以访问任意数据？可以<br>– b17040125用户是否可以访问scott数据<br>– 17040125想要访问scott的表，必须让scott赋予他对应的权限</p>
<p>– 切换到scott用户<br>grant select on emp to b17040125; –开放emp表的查询权限给b17040125<br>grant select on dept to b17040125;<br>grant update on emp to b17040125;<br>grant all on emp to b17040125; –赋予所有权限</p>
<figure class="highlight plain"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br><span class="line">6</span><br></pre></td><td class="code"><pre><span class="line"></span><br><span class="line">### 序列</span><br><span class="line"></span><br><span class="line">-- 用来生成连续整数数据的对象</span><br><span class="line">-- 一般来说序列常常用来作为主键中的增长列</span><br><span class="line">-- 可以升序生成，也可以降序生成</span><br></pre></td></tr></table></figure>
<p>创建序列：（注意序列没有or replace选项）<br>create sequence seq_name<br>start with num<br>increment by 增长间隔    –指定序列锁生成的数字与数字之间的间隔<br>maxvalue num 或者 nomaxvalue –指定序列的最大值，或者指定序列没有最大值<br>minvalue num 或者nominvalue – 指定序列的最小值，或者设置没有最小值<br>cycle 或者 no cycle –指定序列是否有循环<br>cache num 或者 nocache – 指定序列在生成有序数字的是时候是否使用序列号（缓存）</p>
<p>删除序列：</p>
<p>例：在scott用户创建序列<br>grant create sequence to scott;<br>切换到scott用户：<br>create sequence my_seq<br>start with 3<br>minvalue 2<br>maxvalue 20<br>increment by 1<br>cycle<br>nocache<br>– 初始值必须大于等于最小值，小于等于最大值<br>– 如果序列使用循环，从第一次循环开始后，序列的值从最小值开始</p>
<p>– 序列名.nextval() – 取出序列下一个值<br>– 序列名.currval（）–取出当前值在序列没有取出任意一个值的时候，此方法不可使用<br>select my_seq.nextval from dual;<br>select my_seq.currval from dual;</p>
<p>创建一个序列，初始值为1 增长间隔为1，最小值1，没有最大值 不适用缓存，不使用循环<br>建一张表，表中两个字段tid number tname char ，tid主键<br>请你向这张表中插入10条记录，主键在插入的时候使用序列中的数字替代<br>create sequence test_seq<br>start with 1<br>minvalue 1<br>nomaxvalue<br>increment by 1<br>nocache<br>nocycle</p>
<p>create table test (<br>tid number(10) primary key,<br>tname varchar2(20)<br>);</p>
<p>select test_seq.nextval from dual;<br>insert into test values(test_seq.nextval ,’01’);<br>insert into test values(test_seq.nextval ,’02’);<br>insert into test values(test_seq.nextval ,’03’);<br>insert into test values(test_seq.nextval ,’04’);<br>insert into test values(test_seq.nextval ,’05’);<br>insert into test values(test_seq.nextval ,’06’);<br>insert into test values(test_seq.nextval ,’07’);<br>insert into test values(test_seq.nextval ,’08’);<br>insert into test values(test_seq.nextval ,’09’);<br>insert into test values(test_seq.nextval ,’10’);<br>insert into test values(test_seq.nextval ,’11’);<br>select * from test;</p>
<figure class="highlight plain"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br><span class="line">6</span><br><span class="line">7</span><br><span class="line">8</span><br></pre></td><td class="code"><pre><span class="line">### 视图（一张虚拟表，一个复杂的查询结果）</span><br><span class="line">-- 视图（查询语句的一个集合）  </span><br><span class="line">-- create table emp1 as select * from emp;    </span><br><span class="line">只可以复制表的数据，字段和名称，不可以复制约束和外键。  </span><br><span class="line">-- 只想复制表的字段和表名，不想复制表的数据  </span><br><span class="line">create table emp2 as select * from emp where 1&#x3D;2;        </span><br><span class="line">1&#x3D;2返回false，表中没有一条数据满足，自然查询不到数据。</span><br><span class="line"></span><br></pre></td></tr></table></figure>
<p>管理员用户下赋予创建视图权限<br>grant create view to scott;<br>在scott中创建视图：<br>create view view_emp1 as<br>select * from emp1 where deptno =10;<br>select * from view_emp1;</p>
<p>create  view view_emp1_dept as<br>select  e.empno,e.ename,d.deptno from emp1 e join dept d on e.deptno=d.deptno;<br>select * from view_emp1_dept;</p>
<p>创建视图的好处<br>视图可以存储写过的sql语句，当sql语句复杂时，不用再重写该语句，而是使用视图进行数据的查询。<br>1.可以将复杂的查询语句进行查询<br>2.可以有效地保护基表信息不外泄<br>3.屏蔽部分字段</p>
<p>delete from view_emp1_dept where empno=7369;</p>
<p>视图的缺点：<br>1.如果对视图进行增删改查操作，会影响到基表的数据。但是可以在创建视图是使用with read only使视图变为只读。<br>2.视图占用存储空间，查询视图时会查询两次，性能相对于查询差</p>
<p>create  view view_emp1_dept<br>as<br>select  e.empno,e.ename,d.deptno from emp1 e join scott.dept d on e.deptno=d.deptno<br>with read only;</p>
<p>–查询当前用户所有的表<br>select * from all_tables;<br>–查询系统所有的用户<br>select * from all_users; </p>
<p>– 查询管理员视图(切换到管理员用户)<br>select * from sys.all_tables<br>注意在普通用户运行此语句不会查询到管理员的视图，因为没有权限，只会查询到自己的视图。<br>– 查询是否有某张表（注意必须大写）<br>select * from all_tables where table_name=’TEST’;</p>
<figure class="highlight plain"><table><tr><td class="gutter"><pre><span class="line">1</span><br></pre></td><td class="code"><pre><span class="line">### 表空间</span><br></pre></td></tr></table></figure>
<p>–表空间：多个数据文件组成的存储空间<br>–什么是数据文件 a.ora  b.ora c.ora这三个相当于存储数据的容器<br>迅雷下载3个G文件<br>下载时存在.td .temp临时文件，大小3个g，不管有没有下好，先占有3个G的空间<br>– 系统表空间<br>每一个oracle数据库必须具备的<br>存放表空间名称，表空间所含数据文件数据库管理所需信息</p>
<p>– sysaux<br>充当系统表空间的辅助表空间，降低系统表空间的负荷</p>
<p>– 临时表空间<br>用于存储数据库运行期间所产生的临时文件，临时数据会在数据库关闭的时候自动清空<br>如表的数据量非常大，在排序计算的时候会产生大量的运算结果，会消耗大量内存资源，将一部分的运算结果存储在临时空间中，等价于windows的虚拟内存。</p>
<p>– 撤销表空间<br>undotbs</p>
<p>– 用户自定义表空间<br>用来存放用户的数据和私有信息</p>
<p>select distinct tablespace_name from all_tables;<br>select * from all_tables where tablespace_name=’EXAMPLE’;</p>
<p>赋予scott创建表空间权限<br>grant create tablespace to scott;<br>修改/root权限</p>
<figure class="highlight plain"><table><tr><td class="gutter"><pre><span class="line">1</span><br></pre></td><td class="code"><pre><span class="line">![](https:&#x2F;&#x2F;imgconvert.csdnimg.cn&#x2F;aHR0cHM6Ly96emotMTMwMDQwODQ5My5jb3MuYXAtY2hlbmdkdS5teXFjbG91ZC5jb20vJUU1JTlCJUJFJUU1JUJBJThBLzUyLnBuZw?x-oss-process&#x3D;image&#x2F;format,png)  </span><br></pre></td></tr></table></figure>
<p>create tablespace myspace<br>DATAFILE ‘/root/a.ora’ size 10M,<br>‘/root/b.ora’ size 5M   定义的数据文件的最小存储单元<br>extent management local; 以最节省空间的方式存储<br>windows文件系统fat32最小存储单元16字节，支持最大单个文件4G<br>NTFS最小存储单元4KB，支持最大的单个文件大小2TB<br>create tablespace myspace1<br>DATAFILE ‘/root/c.ora’ size 10M,<br>‘/root/d.ora’ size 5M<br>uniform size 1m; 不管文件多大，都会以1m最小存储单元进行存储。<br>– 注意在linux中不要删除.ora数据文件，否则数据库会gg。</p>
<p>在建表时，指定表存储的表空间<br>create table zzj(<br>zid number<br>)<br>tablespace myspace<br>创建用户时指定表空间（如果没有指定表空间，默认存储在users表空间里）<br>create user zzj identified by 123456<br>default tablespace myspace</p>
<p>– 表空间扩容（管理员下）<br>alter database datafile ‘/root/a.ora’ resize 20m;<br>– 增加数据文件<br>alter tablespace myspace add datefile ‘/root/e.ora’ size 10M;<br>启动docker 容器，ctrl+r可以查看历史命令</p>
<figure class="highlight plain"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br><span class="line">6</span><br><span class="line">7</span><br><span class="line">8</span><br><span class="line">9</span><br><span class="line">10</span><br><span class="line">11</span><br><span class="line">12</span><br><span class="line">13</span><br><span class="line">14</span><br><span class="line">15</span><br><span class="line">16</span><br><span class="line">17</span><br><span class="line">18</span><br><span class="line">19</span><br><span class="line">20</span><br><span class="line">21</span><br><span class="line">22</span><br><span class="line">23</span><br></pre></td><td class="code"><pre><span class="line">### 数据库备份（冷备份）</span><br><span class="line">1. 启动sqlplus 以sys as sysdba身份进行登录  </span><br><span class="line">docker exec -it oracle11g &#x2F;bin&#x2F;bash   </span><br><span class="line">source &#x2F;etc&#x2F;profile  </span><br><span class="line">sqlplus &#x2F; as sysdba  </span><br><span class="line">2. 查询当前数据库所有数据文件，控制文件，联机重做日志文件的位置  </span><br><span class="line">select file_name from dba_data_files;    </span><br><span class="line">所有表空间的数据文件位置;  </span><br><span class="line">![](https:&#x2F;&#x2F;imgconvert.csdnimg.cn&#x2F;aHR0cHM6Ly96emotMTMwMDQwODQ5My5jb3MuYXAtY2hlbmdkdS5teXFjbG91ZC5jb20vJUU1JTlCJUJFJUU1JUJBJThBLzUzLlBORw?x-oss-process&#x3D;image&#x2F;format,png)  </span><br><span class="line">select member from v$logfile;    </span><br><span class="line">所有日志文件的查找  </span><br><span class="line">![](https:&#x2F;&#x2F;imgconvert.csdnimg.cn&#x2F;aHR0cHM6Ly96emotMTMwMDQwODQ5My5jb3MuYXAtY2hlbmdkdS5teXFjbG91ZC5jb20vJUU1JTlCJUJFJUU1JUJBJThBLzU0LlBORw?x-oss-process&#x3D;image&#x2F;format,png)  </span><br><span class="line">select value from v$parameter where name&#x3D;&#39;control_files&#39;;  </span><br><span class="line">查找所有控制文件的位置  </span><br><span class="line">![](https:&#x2F;&#x2F;imgconvert.csdnimg.cn&#x2F;aHR0cHM6Ly96emotMTMwMDQwODQ5My5jb3MuYXAtY2hlbmdkdS5teXFjbG91ZC5jb20vJUU1JTlCJUJFJUU1JUJBJThBLzU1LlBORw?x-oss-process&#x3D;image&#x2F;format,png)  </span><br><span class="line"></span><br><span class="line">3. 关闭数据库</span><br><span class="line">shutdown immediate;</span><br><span class="line">4. 回到docker容器中,复制所有数据文件到容器的&#x2F;root&#x2F;bak目录下,再启动sqlplus,最后启动数据库</span><br><span class="line">cp &#x2F;root&#x2F;*.dbf  </span><br><span class="line">![](https:&#x2F;&#x2F;imgconvert.csdnimg.cn&#x2F;aHR0cHM6Ly96emotMTMwMDQwODQ5My5jb3MuYXAtY2hlbmdkdS5teXFjbG91ZC5jb20vJUU1JTlCJUJFJUU1JUJBJThBLzU2LmpwZw?x-oss-process&#x3D;image&#x2F;format,png)  </span><br><span class="line"></span><br><span class="line">### 数据库备份（热备份）</span><br></pre></td></tr></table></figure>
<p>热备份可以在不关机的情况下 完成数据备份</p>
<p>1.启动SQLPLUS  管理员登陆<br>2.查看当前数据库是否启用归档模式<br>archive log list命令去查看数据库是否启用了归档模式 如果没有 需要手动启用<br>alter system set log_archive_start=true scope=spfile;  //归档模式修改<br>shutdown immediate; 关闭数据库<br>startup mount; 启动mount实例<br>alter database archivelog; 更改数据库为归档模式<br>alter database open; 打开数据库<br>archive log list;  检查归档模式是否开启<br>3.以表空间为单位 进行数据文件备份<br>3.1 查看当前数据库有哪些表空间，以及每个表空间中有哪些数据文件<br>select tablespace_name,file_name from dba_data_files order by<br>tablespace_name;</p>
<p>/home/oracle/app/oracle/oradata/helowin/example01.dbf<br>/root/b.ora<br>/root/a.ora<br>/root/c.ora<br>/home/oracle/app/oracle/oradata/helowin/sysaux01.dbf<br>/home/oracle/app/oracle/oradata/helowin/system01.dbf<br>/home/oracle/app/oracle/oradata/helowin/undotbs01.dbf<br>/home/oracle/app/oracle/oradata/helowin/users01.dbf<br>3.2分别对每个表空间中的数据文件进行备份:<br>3.2.1 将需要备份的表空间设置为备份状态<br>alter tablespace users begin backup;<br>alter tablespace MYSPACE begin backup;<br>3.2.2 将表空空间中的所有数据文件复制到备份磁盘</p>
<p>host copy /home/oracle/app/oracle/oradata/helowin/users01.dbf /root<br>结束表空间的备份状态<br>alter tablespace users end backup; 结束备份状态</p>
<figure class="highlight plain"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br><span class="line">6</span><br><span class="line">7</span><br><span class="line">8</span><br><span class="line">9</span><br><span class="line">10</span><br><span class="line">11</span><br><span class="line">12</span><br><span class="line">13</span><br><span class="line">14</span><br></pre></td><td class="code"><pre><span class="line">### 数据库结构</span><br><span class="line">![](https:&#x2F;&#x2F;imgconvert.csdnimg.cn&#x2F;aHR0cHM6Ly96emotMTMwMDQwODQ5My5jb3MuYXAtY2hlbmdkdS5teXFjbG91ZC5jb20vJUU1JTlCJUJFJUU1JUJBJThBLzUwLmpwZw?x-oss-process&#x3D;image&#x2F;format,png)  </span><br><span class="line">数据库逻辑结构：  </span><br><span class="line">![](https:&#x2F;&#x2F;imgconvert.csdnimg.cn&#x2F;aHR0cHM6Ly96emotMTMwMDQwODQ5My5jb3MuYXAtY2hlbmdkdS5teXFjbG91ZC5jb20vJUU1JTlCJUJFJUU1JUJBJThBLzUxLmpwZw?x-oss-process&#x3D;image&#x2F;format,png)</span><br><span class="line">### 索引</span><br><span class="line">索引  </span><br><span class="line">- 通过索引 可以帮助我们提高查询的效率，减少对数据的遍历  </span><br><span class="line">- 5000条数据 select * from test where no&#x3D;20;</span><br><span class="line">- 5000页：数据量巨大时，使用索引成为必要  </span><br><span class="line">- 磁盘上的数据块与链表相似，包含一个数据段和一个指针，指针指向下一个（数据块地址）</span><br><span class="line">- 索引是建立在我们的字段上的，当我们使用建立了索引的字段进行查询时就会触发索引，会使用索引进行查询，不会进行全表遍历，极大地提高了查询效率。</span><br><span class="line">- 每一行数据都会有一个对应的rowid，预先按照一定的规则（二叉树，b-tree，bitmap，hash）对建立了索引的字段进行排序，当你使用建立了索引的字段进行查询时，可以直接通过预先排序好的字段值找到对应的rowid，通过rowid直接获取到查找的数据</span><br><span class="line">- 每一次对建立了索引的表进行增删改时候，都会破坏索引。</span><br><span class="line">- 索引不适合频繁的进行增删改的表，也不适合数据量较小的表</span><br></pre></td></tr></table></figure>
<p>普通索引（建立普通索引的字段是可以有重复值的）<br>create index index_emp1 on emp1(ename);<br>唯一索引（只能建立在没有重复值的字段或者是添加了唯一约束的字段上）<br>create unique index uni_index_emp1 on emp1(empno);<br>oracle数据库中默认对所有主键和唯一约束的字段添加索引。</p>
<p>– 创建唯一索引<br>create unique index index_001 on tb_student(name)<br>– 组合索引<br>create unique index index_001 on tb_student(name,age)</p>
<p>– 删除索引<br>drop index index_001;</p>
<figure class="highlight plain"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br><span class="line">6</span><br><span class="line">7</span><br><span class="line">8</span><br><span class="line">9</span><br><span class="line">10</span><br><span class="line">11</span><br><span class="line">12</span><br><span class="line">13</span><br><span class="line">14</span><br><span class="line">15</span><br><span class="line">16</span><br><span class="line">17</span><br><span class="line">18</span><br><span class="line">19</span><br><span class="line">20</span><br><span class="line">21</span><br><span class="line">22</span><br><span class="line">23</span><br><span class="line">24</span><br><span class="line">25</span><br><span class="line">26</span><br><span class="line">27</span><br><span class="line">28</span><br><span class="line">29</span><br><span class="line">30</span><br><span class="line">31</span><br><span class="line">32</span><br><span class="line">33</span><br><span class="line">34</span><br><span class="line">35</span><br><span class="line">36</span><br></pre></td><td class="code"><pre><span class="line">### SQL优化  </span><br><span class="line">1. 查询语句尽量不要使用* 而是select具体字段 </span><br><span class="line">2. 统计行数时，count(1)比count(*)</span><br><span class="line">3. 尽量避免在where子句中使用or进行条件链接 or会使数据库放弃使用索引，直接使用全盘扫描</span><br><span class="line">4. like语句%不可以放在第一位</span><br><span class="line">   1. select * from emp where emp like &#39;%123&#39;不会使用索引</span><br><span class="line">5. 使用where条件限定查询的数据，避免返回多余行</span><br><span class="line">6. 尽量避免在建立索引的字段上使用函数</span><br><span class="line">7. 尽量避免在where条件中使用!&#x3D;或者&lt;&gt;同样会使索引失效;</span><br><span class="line">8. 尽量在where和order by 涉及到的字段上建立索引，可以最大程度去避免全盘扫描</span><br><span class="line">9. 慎重使用distinct关键字</span><br><span class="line">10. 不要有超过5个以上的笛卡尔积</span><br><span class="line">11. 索引不要太多，一般在五个以内</span><br><span class="line">12. 尽量用数字型字段，若只包含数字信息，尽量不要设计为字符型</span><br><span class="line">13. 当在sql语句中连接多个表时，请使用表的别名，并把别名前缀于每一列上，这样语义更加清晰</span><br><span class="line">反例：  </span><br><span class="line">&#96;select * from A inner join B on A.deptId&#x3D;B.deptId;&#96;  </span><br><span class="line">正例：  </span><br><span class="line">&#96;select member.name,deptment.deptName from A member inner join B deptment on member.deptId &#x3D; deptment.deptId&#96;</span><br><span class="line">14. 为了提高group by 的效率在执行分组语句之前，把不需要使用的数据给剔除掉。</span><br><span class="line">反例：  </span><br><span class="line">&#96;select job,avg(sal) from emp group by job having job&#x3D;&#39;president&#39; or job &#x3D; &#39;managent&#39;&#96;  </span><br><span class="line">正例：  </span><br><span class="line">&#96;select job, avg(sal) from emp where job&#x3D;&#39;president&#39; or job&#x3D;&#39;managent&#39; group by job;&#96;</span><br><span class="line">15.尽量用&gt;&#x3D;代替&gt;</span><br><span class="line">16.使用表的别名，将之作为每列的前缀</span><br><span class="line"></span><br><span class="line">## plsql（数据库数据处理的编程语言）</span><br><span class="line">1. 编程语言分类：强类型语言和弱类型语言</span><br><span class="line">   1. 强类型：定义变量时必须同时定义变量的数据类型 plsql,java，c,c++</span><br><span class="line">   2. 弱类型:变量的数据类型有变量的值决定，不需要进行数据类型转换 shell,javascript</span><br><span class="line">2. 变量声明</span><br><span class="line">   1. PL&#x2F;SQL支持SQL中所有数据类型（number,varchar2,char,date）</span><br><span class="line">   2. declare:负责声明变量</span><br><span class="line">   3. begin：负责提供具体的代码</span><br><span class="line">   4. end</span><br></pre></td></tr></table></figure>
<p>set serveroutput on;  –设置在控制台输出，否则会在后端输出<br>declare<br>name varchar2(20):=’B17040125’;<br>begin<br>dbms_output.put_line(name);<br>end;</p>
<p>对变量赋值还可以使用select into 语句<br>从数据库中查询数据对变量进行赋值<br>但是查询的结果只能是一行记录，不能是0行或者多行</p>
<p>set serveroutput on;<br>declare<br>sname varchar2(20):=’wzh’;<br>begin<br>select ename into sname from emp where empno=7934;<br>–查询编号为7934的员工的ename属性值，并将值赋给sname变量<br>dbms_output.put_line(sname);<br>end;</p>
<figure class="highlight plain"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br></pre></td><td class="code"><pre><span class="line">3. 常量</span><br><span class="line">   1. 在声明时赋予初值，并且在运行时不能重新赋值</span><br><span class="line">   2. 使用constant关键字声明变量</span><br></pre></td></tr></table></figure>
<p>set serveroutput on;<br>declare<br>r constant number(10):=10;<br>begin<br>r:=20; //报错，常量值不能修改<br>dbms_output.put_line(r);<br>end;</p>
<figure class="highlight plain"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br><span class="line">6</span><br><span class="line">7</span><br><span class="line">8</span><br></pre></td><td class="code"><pre><span class="line">4. plsql自带数据类型</span><br><span class="line">   1. 标量数据类型：标量只有一个值，包括number,char,varchar2,date,boolean</span><br><span class="line">   2. POSTIVE:正整数类型</span><br><span class="line">   3. 属性数据类型</span><br><span class="line">      1. 当声明的一个变量的值是数据库中的一行或者是数据库中的某一列时，可以使用属性类型进行声明</span><br><span class="line">      2. 两种属性类型</span><br><span class="line">         1. %ROWTYPE 类型的变量，用于存储查询结果中的一行数据，也叫record类型（查询并赋值一行）</span><br><span class="line">         2. %TYPE类型，可以引用表中某字段的类型作为变量的数据类型，也可以引用某变量的数据类型作为新变量的数据类型。</span><br></pre></td></tr></table></figure>
<p>set serveroutput on;<br>declare<br>myemp emp%rowtype;<br>– 定义一个变量叫做myemp，这个变量用来存储emp表的一行记录<br>begin<br>select * into myemp from emp where empno=7934;<br>– select * into myemp from emp；也会报错，因为rowtype只能存储一行数据<br>dbms_output.put_line(myemp.ename);<br>– 注意不能直接打印myemp，因为putline只能打印字符串<br>end;</p>
<p>set serveroutput on;<br>declare<br>sal emp.sal%type;<br>–等价于：sal number(7,2)<br>– 定义一个变量，使用scott用户的emp表的sal的字段的数据类型，作为自己的数据类型<br>mysal number(4):=3000;<br>totalsal mysal%type; –等价于totalsal number(4)<br>begin<br>select sal into sal from emp where empno=7934;<br>totalsal:=sal+mysal;<br>dbms_output.put_line(totalsal);<br>end;</p>
<figure class="highlight plain"><table><tr><td class="gutter"><pre><span class="line">1</span><br></pre></td><td class="code"><pre><span class="line">5. plsql同时也有其他编程语言所具备的：条件控制，循环控制</span><br></pre></td></tr></table></figure>
<p>顺序结构<br>条件结构<br>循环结构<br>if then<br>if then else endif;<br>if then elsif then endif;（注意是elsif）</p>
<p>set serveroutput on;<br>declare<br>newsal emp.sal%type;<br>begin<br>select sal into newsal from emp where ename=’JAMES’;<br>if newsal &gt;2000 then<br>– if后面的语句  条件运算式返回布尔值<br>dbms_output.put_line(‘james很有钱’);<br>else<br>dbms_output.put_line(‘james很穷’);<br>end if;<br>end;</p>
<p>–查询JAMES工资，如果大于900则将JAMES的奖金修改为800元，否则把comm奖金修改为400<br>set serveroutput on;<br>declare<br>comm emp.comm%type;<br>begin<br>select comm into comm from emp where ename=’JAMES’;<br>if comm &gt;900 then<br>update emp set comm=800 where ename=’JAMES’;<br>else<br>update emp set comm=800 where ename=’JAMES’;<br>end if;<br>dbms_output.put_line(comm);<br>end;</p>
<hr>
<p>case [selector]<br>when 表达式1 then 语句1<br>when 表达式2 then 语句2<br>end case;</p>
<p>set serveroutput on;<br>declare<br>v_grade char(1):=’A’;<br>begin<br>case v_grade<br>  when ‘A’ then dbms_output.put_line(‘优秀’);<br>  when ‘B’ then dbms_output.put_line(‘良好’);<br>  when ‘C’ then dbms_output.put_line(‘合格’);<br>  else<br>    dbms_output.put_line(‘没有该成绩’);<br>end case;<br>end;</p>
<p>plsql 提供三种循环结构进行语句的重复执行<br>1.无条件循环loop  end loop –死循环<br>2.while循环<br>3.for语句</p>
<p>– 计算1-100的值，loop<br>set serveroutput on;<br>declare<br>counter number(3):=0;<br>sumResult number :=0;<br>begin<br>loop<br>counter:=counter+1;<br>sumResult := sumResult + counter;<br>if counter&gt;=100 then<br>exit;<br>end if;<br>//exit when counter &gt;=100;<br>end loop;<br>dbms_output.put_line(sumResult);<br>end;</p>
<hr>
<p>–while循环<br>set serveroutput on;<br>declare<br>counter number(3):=0;<br>sumResult number:=0;<br>begin<br>while counter &lt; 100<br>loop<br>  counter:=counter+1;<br>  sumResult:=sumResult+counter;<br>end loop;<br>dbms_output.put_line(sumResult);<br>end;</p>
<figure class="highlight plain"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br><span class="line">6</span><br></pre></td><td class="code"><pre><span class="line">6. 触发器</span><br><span class="line">   1. 触发器是一种在事件发生时隐式自动执行的PLSQL块，不接受参数，不能被显示调用。</span><br><span class="line">   2. 触发器类型：按触发条件不同分为三类：</span><br><span class="line">      1. DML触发器：当你对指定的表进行DML操作时，所触发的触发器</span><br><span class="line">      2. 语句级触发器或者行触发器：行触发器会对数据库表中的受影响的每一行触发一次触发器代码，语句级触发器只能触发一次。</span><br><span class="line">      3. before触发器或者after触发器：before在触发事件之前执行触发器代码，after在触发事件之后执行触发器代码</span><br></pre></td></tr></table></figure>
<p>   1）DML触发器<br>   grant create trigger to scott;  –赋予创建触发器的权限<br>   create [or replace] trigger<br>   {before|after}<br>   trigger_event –触发条件<br>   on table_name<br>   [for each row]<br>   [when trigger_condition]<br>   trigger_body</p>
<p>   2)替代触发器：对视图操作时定义的触发器<br>   3）系统事件触发器</p>
<p>–触发器案例<br>student stu_log<br>create table student(   –创建student表<br>id number(19),<br>stu_no varchar2(20),<br>stu_name varchar2(20),<br>stu_age number,<br>stu_major varchar2(32)<br>);</p>
<p>create table stu_log(   – 创建stu_log表<br>log_id number,–日志id<br>log_action varchar2(100),–操作名称<br>log_date date,–操作时间<br>log_message varchar2(32)– 操作信息<br>);</p>
<p>a)行级触发器（before触发器）<br>实现id的隐式自增 结合序列加上触发器 实现该功能<br>create sequence seq_student<br>start with 1<br>minvalue 1<br>nomaxvalue<br>increment by 1<br>nocycle<br>nocache</p>
<p>create or replace trigger modify_stu<br>before<br>insert<br>on student –该触发器在向学生表插入信息之前就触发<br>for each row –每一行后触发该条件<br>declare<br>next_id number;<br>begin<br>select seq_student.nextval into next_id from dual;<br>– 从序列中取出一个值并且赋值给next_id<br>:new.id:=next_id;<br>– :new对应的是即将插入的语句，：new.id对应的是该语句的id字段<br>– 将上一步序列中取出的值替换即将插入的语句的id值<br>end;<br>–插入数据，不插入id<br>insert into student (stu_no,stu_name,stu_age,stu_major) values (‘NO1’,’zzj’,20,’计算机’);<br>select * from student;<br>–记得commit;</p>
<p>–所有对student表的操作，都会记录到stu_log表中<br>create or replace trigger modify_stu1<br>after insert or delete or update of stu_name on student–在任何人对学生姓名属性进行DML操作之后<br>for each row<br>begin<br>if inserting then –如果触发器观察到进行的DML操作是insert操作<br>insert into stu_log values(1,’insert’,sysdate,:new.stu_name);<br>– ：new.stu_name 表示的是你的插入语句中的stu_name的值<br>elsif deleting then – 如果观察到的操作是delete操作<br>insert into stu_log values(2,’delete’,sysdate,:old.stu_name);<br>– old.stu_name获取的是你删除的语句的stu_name<br>elsif updating then<br>insert into stu_log values(3,’update_old’,sysdate,:old.stu_name);–被更新之前的学生姓名<br>insert into stu_log values(4,’update_new’,sysdate,:new.stu_name);<br>end if;<br>end;</p>
<p>insert into student (stu_no,stu_name,stu_age,stu_major) values(‘N2’,’ada’,18.’java’);<br>insert into student (stu_no,stu_name,stu_age,stu_major) values(‘N2’,’ad’,18.’java’);<br>delete student where stu_name=’ada’;<br>update student set stu_age=18 where stu_name=’ad’;<br>select * from stu_log</p>
<p>3）语句级触发器(before触发器)用来控制对表的修改<br>create or replace trigger modify_stu2<br>before insert or update or delete on student<br>begin<br>  if deleting then<br>  raise_application_error(-20001,’该表不允许删除数据’);<br>  – 产生一个用户自定义错误<br>  elsif updating then<br>  raise_application_error(-20002,’该表不允许删除数据’);<br>  elsif inserting then<br>  raise_application_error(-20003,’该表不允许删除数据’);<br>  end if;<br>end;</p>
<p>– 这里注意别忘了end if ,且end if中间有空格隔开。</p>
<figure class="highlight plain"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br></pre></td><td class="code"><pre><span class="line">## 游标</span><br><span class="line">-- 对多行数据进行处理时候：游标，指针  </span><br><span class="line">1.查询前十名员工的信息  </span><br><span class="line"></span><br></pre></td></tr></table></figure>
<p>set serveroutput on;<br>declare<br>  cursor c_cursor is<br>  select ename,sal from emp where rownum&lt;11;<br>  v_ename emp.ename%type;<br>  v_sal emp.sal%type;<br>begin<br>open c_cursor;<br>fetch c_cursor into v_ename,v_sal;<br>while c_cursor%FOUND loop<br>–c_cursor%FOUND判断指针指向的数据是否为空<br>dbms_output.put_line(v_ename||’—‘||v_sal);<br>fetch c_cursor into v_ename,v_sal;<br>end loop;<br>close c_cursor;<br>end;</p>
<figure class="highlight plain"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br><span class="line">6</span><br><span class="line">7</span><br><span class="line">8</span><br><span class="line">9</span><br><span class="line">10</span><br><span class="line">11</span><br><span class="line">12</span><br><span class="line">13</span><br></pre></td><td class="code"><pre><span class="line"></span><br><span class="line"></span><br><span class="line">2. 游标属性  </span><br><span class="line">游标名称%FOUND 布尔值类型 当最近一次提取数据操作fetch成功则返回true，否则返回false  </span><br><span class="line">游标名称%NOTFOUND 布尔值类型 与%FOUND相反  </span><br><span class="line">游标名称%ISOPEN 布尔值类型 判断有标是否打开，如果打开返回true，否则返回false  </span><br><span class="line">游标名称%ROWCOUNT 数字属性 返回已经从游标中提取记录的数量  </span><br><span class="line"></span><br><span class="line">3. 给所有工资低于1200的员工加50工资  </span><br><span class="line">先通过游标读取所有员工sal数据，遍历进行比对  </span><br><span class="line">如果有低于1200  </span><br><span class="line">的，则进行update语句  </span><br><span class="line"></span><br></pre></td></tr></table></figure>
<p>declare<br>cursor c_cur is select empno,sal from emp;<br>v_empno emp.empno%type;<br>v_sal emp.sal%type;<br>begin<br>open c_cur;<br>loop<br>fetch c_cur into v_empno,v_sal;<br>exit when c_cur%NOTFOUND;<br>if v_sal&lt;1200 then<br>update emp set sal=sal+50 where empno=v_empno;<br>end if;<br>commit;<br>end loop;<br>close c_cur;<br>end;</p>
<figure class="highlight plain"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br></pre></td><td class="code"><pre><span class="line"></span><br><span class="line"></span><br><span class="line">4. 游标的for循环su  </span><br><span class="line">遍历emp表中所有员工的部门编号，姓名，薪水  </span><br><span class="line"></span><br></pre></td></tr></table></figure>
<p>declare<br>cursor c_sal is select empno,ename,sal from emp;<br>begin<br>for v_sal in c_sal loop –隐含了打开游标<br>– 相当于在for定义了一个rowtype变量，变量名为v_sal<br>  dbms_output.put_line(v_sal.empno||’—‘||v_sal.ename||’—‘||v_sal.sal);<br>  – 隐含了一个c_sal%notfound<br>  end loop;<br>  – 隐含了关闭游标<br>end;</p>
<figure class="highlight plain"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br></pre></td><td class="code"><pre><span class="line"></span><br><span class="line"></span><br><span class="line">--查询所有员工的数据，将所有工资大于等于平均工资的员工sal-500，将所有比平均工资低的员工+50  </span><br><span class="line"></span><br></pre></td></tr></table></figure>
<p>declare<br>cursor c_sal is select e.av,p.sal,p.empno from(select avg(sal)av from emp)e natural join (select sal,empno from emp)p;<br>begin<br>for v_sal in c_sal loop –隐含了打开游标<br>– 相当于在for定义了一个rowtype变量，变量名为v_sal<br>  if v_sal.sal&gt;=v_sal.av then<br>  update emp set sal=sal-500 where empno=v_sal.empno ;<br>  elsif v_sal.sal&lt; v_sal.av then<br>  update emp set sal=sal+50 where empno=v_sal.empno;<br>  – 隐含了一个c_sal%notfound<br>  end if;<br>  end loop;<br>  – 隐含了关闭游标<br>end;</p>
<p>declare<br>cursor c_ur<br>is<br>select empno,sal from emp;<br>acg_sal emp.sal%type;<br>begin<br>select avg(sal) into avg_sal from emp;<br>for v_sal in c_ur loop –隐含了打开游标<br>– 相当于在for定义了一个rowtype变量，变量名为v_sal<br>  if v_sal.sal&gt;=avg_sal then<br>  update emp set sal=sal-500 where empno=v_sal.empno ;<br>  commit;<br>  else<br>  update emp set sal=sal+50 where empno=v_sal.empno;<br>  – 隐含了一个c_sal%notfound<br>  commit;<br>  end if;<br>  end loop;<br>  – 隐含了关闭游标<br>end;</p>
<figure class="highlight plain"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br><span class="line">6</span><br></pre></td><td class="code"><pre><span class="line"></span><br><span class="line"></span><br><span class="line">## 存储过程</span><br><span class="line">存储过程：对PL&#x2F;SQL代码的封装  </span><br><span class="line">-- 统计emp表的雇员总人数  </span><br><span class="line"></span><br></pre></td></tr></table></figure>
<p>grant create procedure to scott;<br>create or replace procedure emp_count<br>as<br>v_total number(10);<br>begin<br>select count(*) into v_total from emp;<br>dbms_output.put_line(‘雇员总数为：’||v_total);<br>end;</p>
<p>set serveroutput on;<br>execute emp_count;</p>
<figure class="highlight plain"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br><span class="line">6</span><br></pre></td><td class="code"><pre><span class="line"></span><br><span class="line"></span><br><span class="line">存储过程中还可以调用存储过程  </span><br><span class="line">编写一个显示雇员信息的存储过程emp_list  </span><br><span class="line">并引用emp_count存储过程  </span><br><span class="line"></span><br></pre></td></tr></table></figure>
<p>create or replace procedure<br>emp_list<br>as<br>cursor emp_cursor is select empno,ename from emp;<br>begin<br>for emp_record in emp_cursor<br>loop<br>dbms_output.put_line(emp_record.empno||’–’||emp_record.ename);<br>end loop;<br>emp_count;–调用其他存储过程<br>end;</p>
<p>set serveroutput on;<br>execute emp_list;</p>
<figure class="highlight plain"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br><span class="line">6</span><br><span class="line">7</span><br><span class="line">8</span><br><span class="line">9</span><br><span class="line">10</span><br></pre></td><td class="code"><pre><span class="line"></span><br><span class="line"></span><br><span class="line">编写显示部门信息的存储过程DEPT_LIST  </span><br><span class="line">要求可以显示所有部门的deptno,dname以及部门总数  </span><br><span class="line">in参数：定义一个输入参数变量，用于传递参数给存储过程  </span><br><span class="line">out参数：从存储过程中获取数据  </span><br><span class="line">in out参数：兼备两者功能  </span><br><span class="line">-- 编写一个给员工增加工资的存储过程change_sal  </span><br><span class="line">参数  </span><br><span class="line"></span><br></pre></td></tr></table></figure>
<p>create or replace procedure<br>change salsry(p_empno in number default 7788,p_raise number default 10)<br>as<br>v_ename varchar2(10);<br>v_sal number(5);<br>begin<br>select ename,sal into v_ename,v_sal from emp where empno=p_empno;<br>update emp set sal=sal+p_raise where empno=p_empno;<br>end;</p>
<p>– 统计emp表的部门总数<br>create or replace procedure dept_count<br>as<br>v_total number(10);<br>begin<br>select count(distinct(deptno)) into v_total from dept;<br>dbms_output.put_line(‘雇员总数为：’||v_total);<br>end;</p>
<p>create or replace procedure<br>dept_list<br>as<br>cursor dept_cursor is select dname,deptno from dept;<br>begin<br>for dept_record in dept_cursor<br>loop<br>dbms_output.put_line(dept_record.deptno||’–’||dept_record.dname);<br>end loop;<br>dept_count;–调用其他存储过程<br>end;<br>set serveroutput on;<br>execute dept_list;</p>
<pre><code>

## 存储函数





</code></pre>

    </div>

    
    
    

    <footer class="post-footer">
          

<div class="post-copyright">
<ul>
  <li class="post-copyright-author">
      <strong>本文作者： </strong>Carson Zhao
  </li>
  <li class="post-copyright-link">
      <strong>本文链接：</strong>
      <a href="https://bone-nails.gitee.io/2021/04/01/%E6%95%B0%E6%8D%AE%E5%BA%93%E6%BC%94%E7%BB%83oracle11g/" title="数据库演练oracle11g">https://bone-nails.gitee.io/2021/04/01/数据库演练oracle11g/</a>
  </li>
  <li class="post-copyright-license">
    <strong>版权声明： </strong>本博客所有文章除特别声明外，均采用 <a href="https://creativecommons.org/licenses/by-nc-sa/4.0/deed.zh" rel="noopener" target="_blank"><i class="fab fa-fw fa-creative-commons"></i>BY-NC-SA</a> 许可协议。转载请注明出处！
  </li>
</ul>
</div>

          <div class="post-tags">
              <a href="/hexo_blog/tags/Oracle/" rel="tag"># Oracle</a>
          </div>

        

          <div class="post-nav">
            <div class="post-nav-item">
                <a href="/hexo_blog/2021/04/01/centos%E5%AE%89%E8%A3%85%E5%92%8C%E7%BD%91%E7%BB%9C%E9%85%8D%E7%BD%AE/" rel="prev" title="centos安装和网络配置">
                  <i class="fa fa-chevron-left"></i> centos安装和网络配置
                </a>
            </div>
            <div class="post-nav-item">
                <a href="/hexo_blog/2021/04/01/Linux%E7%9F%A5%E8%AF%86%E6%95%B4%E7%90%862/" rel="next" title="Linux知识整理2">
                  Linux知识整理2 <i class="fa fa-chevron-right"></i>
                </a>
            </div>
          </div>
    </footer>
  </article>
</div>







<script>
  window.addEventListener('tabs:register', () => {
    let { activeClass } = CONFIG.comments;
    if (CONFIG.comments.storage) {
      activeClass = localStorage.getItem('comments_active') || activeClass;
    }
    if (activeClass) {
      const activeTab = document.querySelector(`a[href="#comment-${activeClass}"]`);
      if (activeTab) {
        activeTab.click();
      }
    }
  });
  if (CONFIG.comments.storage) {
    window.addEventListener('tabs:click', event => {
      if (!event.target.matches('.tabs-comment .tab-content .tab-pane')) return;
      const commentClass = event.target.classList[1];
      localStorage.setItem('comments_active', commentClass);
    });
  }
</script>
</div>
  </main>

  <footer class="footer">
    <div class="footer-inner">


<div class="copyright">
  &copy; 
  <span itemprop="copyrightYear">2021</span>
  <span class="with-love">
    <i class="fa fa-heart"></i>
  </span>
  <span class="author" itemprop="copyrightHolder">Carson Zhao</span>
</div>
<div class="wordcount">
  <span class="post-meta-item">
    <span class="post-meta-item-icon">
      <i class="fa fa-chart-line"></i>
    </span>
      <span>站点总字数：</span>
    <span title="站点总字数">125k</span>
  </span>
  <span class="post-meta-item">
    <span class="post-meta-item-icon">
      <i class="fa fa-coffee"></i>
    </span>
      <span>站点阅读时长 &asymp;</span>
    <span title="站点阅读时长">1:54</span>
  </span>
</div>
<div class="busuanzi-count">
    <span class="post-meta-item" id="busuanzi_container_site_uv" style="display: none;">
      <span class="post-meta-item-icon">
        <i class="fa fa-user"></i>
      </span>
      <span class="site-uv" title="总访客量">
        <span id="busuanzi_value_site_uv"></span>
      </span>
    </span>
    <span class="post-meta-item" id="busuanzi_container_site_pv" style="display: none;">
      <span class="post-meta-item-icon">
        <i class="fa fa-eye"></i>
      </span>
      <span class="site-pv" title="总访问量">
        <span id="busuanzi_value_site_pv"></span>
      </span>
    </span>
</div>
  <div class="powered-by">由 <a href="https://hexo.io/" class="theme-link" rel="noopener" target="_blank">Hexo</a> & <a href="https://theme-next.js.org/" class="theme-link" rel="noopener" target="_blank">NexT.Gemini</a> 强力驱动
  </div>

    </div>
  </footer>

  
  <script size="300" alpha="0.6" zIndex="-1" src="https://cdn.jsdelivr.net/npm/ribbon.js@1.0.2/dist/ribbon.min.js"></script>
  <script src="https://cdn.jsdelivr.net/npm/animejs@3.2.1/lib/anime.min.js"></script>
<script src="/hexo_blog/js/utils.js"></script><script src="/hexo_blog/js/next-boot.js"></script>

  
<script src="/hexo_blog/js/local-search.js"></script>



<script>
if (document.querySelectorAll('.pdf-container').length) {
  NexT.utils.getScript('https://cdn.jsdelivr.net/npm/pdfobject@2.2.4/pdfobject.min.js', () => {
    document.querySelectorAll('.pdf-container').forEach(element => {
      PDFObject.embed(element.dataset.target, element, {
        pdfOpenParams: {
          navpanes : 0,
          toolbar  : 0,
          statusbar: 0,
          pagemode : 'thumbs',
          view     : 'FitH'
        },
        PDFJS_URL: '/hexo_blog/lib/pdf/web/viewer.html',
        height   : element.dataset.height
      });
    });
  }, window.PDFObject);
}
</script>


  <script>
    NProgress.configure({
      showSpinner: true
    });
    NProgress.start();
    document.addEventListener('readystatechange', () => {
      if (document.readyState === 'interactive') {
        NProgress.inc(0.8);
      }
      if (document.readyState === 'complete') {
        NProgress.done();
      }
    });
    document.addEventListener('pjax:send', () => {
      NProgress.start();
    });
    document.addEventListener('pjax:success', () => {
      NProgress.done();
    });
  </script>

  
  <script async src="https://busuanzi.ibruce.info/busuanzi/2.3/busuanzi.pure.mini.js"></script>





<script src="/hexo_blog/live2dw/lib/L2Dwidget.min.js?094cbace49a39548bed64abff5988b05"></script><script>L2Dwidget.init({"pluginModelPath":"assets/","model":{"jsonPath":"/hexo_blog/live2dw/assets/hijiki.model.json"},"display":{"position":"right","width":200,"height":400},"mobile":{"show":true},"log":false,"pluginJsPath":"lib/","pluginRootPath":"live2dw/","tagMode":false});</script></body>
</html>
